agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Iuri Sampaio <iuri.sampaio@gmail.com>
To: pgsql-sql@postgresql.org
Subject: total and partial sums in the same query??
Date: Fri, 9 Oct 2020 23:58:15 -0300
Message-ID: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com> (raw)
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
view thread (6+ messages) latest in thread
Message-ID: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com>
Permalink: ../88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com/
Also on: postgresql.org/message-id/88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iuri.sampaio@gmail.com
Subject: Re: total and partial sums in the same query??
In-Reply-To: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox