total and partial sums in the same query??

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

Responses

Browse pgsql-sql by date

  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)