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) , ….
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