Re: total and partial sums in the same query?? - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: total and partial sums in the same query?? |
| Date | |
| Msg-id | CAKFQuwZg9Wpt_BsxvAFStBcvBO6DkM7yCt1hGe5A9PmLLY1PUQ@mail.gmail.com Whole thread Raw |
| In response to | total and partial sums in the same query?? (Iuri Sampaio <iuri.sampaio@gmail.com>) |
| Responses |
Re: total and partial sums in the same query??
|
| List | pgsql-sql |
On Fri, Oct 9, 2020 at 7:58 PM Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
Is there a way to return total and partial sums (grouped by a third column) in the same query?
Yes.
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 however, it was not possible to recognize the partition
You should observe the version numbers when viewing documentation and try and use either the most current docs or the version you are coding against. If you are indeed coding against 9.1 be advised it is considerably out-of-date.
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...
You forget the keyword "BY" in "PARTITION BY". That explains the immediate syntax error message. You would get many more errors due to having made up the entire contents of the PARTITION BY portion of the window definition (partitions are not specified using a full select-like statement, the trailing semicolon in there is also a problem). You need to consult the SQL Command reference documentation, in this case SELECT, to get the full syntax for stuff - tutorials are not necessarily comprehensive.
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}
This detail seems immaterial to the immediate question at hand. A self-contained problem (see WITH/CTE) with fewer complex expressions generally makes learning, and asking for help, easier.
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 partialFROM qt_vehicle_ti GROUP BY type;type | count | partial------------+--------+--------------Bus | 6702 | 8779Car | 191761 | 8779
Motorbike | 3746 | 8779SUV/Pickup | 22536 | 8779
Truck | 21801 | 8779
Unknown | 588341 | 8779
Van | 7951 | 8779
What about the above example, assuming it is indeed something that works, is wrong?
Between subqueries, window functions, and group by you've got the tools pretty well identified. If you want help putting them together you should construct a simplified self-contained example and, using the provided input data, describe exactly what the output table needs to show.
David J.