Re: total and partial sums in the same query??

On Fri, Oct 9, 2020 at 7:58 PM Iuri Sampaio <iuri(dot)sampaio(at)gmail(dot)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 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
>
>
>
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.