From: | Iuri Sampaio <iuri(dot)sampaio(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | total and partial sums in the same query?? |
Date: | 2020-10-10 02:58:15 |
Message-ID: | 88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Is there a way to return total and partial sums (grouped by a third column) in the same query?
Total is an aggregate function i.e. COUNT(1), partial is some sort of conditional as in: CASE WHEN EXTRACT(MONTH FROM date) = 10 THEN COUNT(1) , ….
I've tried to Window functions https://www.postgresql.org/docs/9.1/tutorial-window.html <https://www.postgresql.org/docs/9.1/tutorial-window.html> however, it was not possible to recognize the partition
SELECT split_part(description, ' ', 25) AS type, COUNT(1), COUNT(1) OVER (PARTITION split_part(description, ' ', 25) WHERE EXTRACT(MONTH FROM creation_date::date) = 10 AS TotalOctober FROM qt_vehicle_ti GROUP BY type;
);
ERROR: syntax error at or near "split_part"
LINE 1: ... 25) AS type, COUNT(1), COUNT(1) OVER (PARTITION split_part...
The column “description" is manipulated with split_part to allow GROUP BY to sort and count by categories, which is one word among others within the description column, as in .
{id 7281 plate_number FRP380 first_seen {2020-07-15 14:50:26} last_seen {2020-07-15 14:50:26} probability 0.6 location_name Test camera_name LPR4 direction LEAVING class Car}
So, the result must be something like the result bellow
SELECT split_part(description, ' ', 25) AS type,
COUNT(1) AS total,
(
SELECT COUNT(1) as partial FROM qt_vehicle_ti v2 WHERE split_part(v2.description, ' ', 25) = split_part(description, ' ', 25) AND EXTRACT(MONTH FROM v2.creation_date::date) = 10
) AS partial
FROM qt_vehicle_ti GROUP BY type;
type | count | partial
------------+--------+--------------
Bus | 6702 | 8779
Car | 191761 | 8779
Motorbike | 3746 | 8779
SUV/Pickup | 22536 | 8779
Truck | 21801 | 8779
Unknown | 588341 | 8779
Van | 7951 | 8779
Best wishes,
I
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-10-10 03:40:36 | Re: total and partial sums in the same query?? |
Previous Message | James Cloos | 2020-10-09 20:01:27 | getting aroung chr(0) |