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

On Friday, October 9, 2020, Iuri Sampaio <iuri(dot)sampaio(at)gmail(dot)com> wrote:

> Hi David,
>
> RIGHT OUTER JOIN is the key!
>
> TOTAL
> SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM
> qt_vehicle_ti GROUP BY type
>
> OCTOBER
> SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM
> qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) = 10 GROUP BY type
>
>
> FINAL
> SELECT split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) AS
> total FROM qt_vehicle_ti v RIGHT OUTER JOIN ( SELECT
> split_part(description, ' ', 25) AS type1, COUNT(1) AS partial FROM
> qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) = 10 GROUP BY type1)
> AS t ON t.type1 = split_part(v.description, ' ', 25) GROUP BY type, partial
>
>
>
> Let me know if you would use a different approach
>

The convention I try to observe when using outer joins is to use left join,
not right (outer is implied). That said, you seem to have written a left
join query since the totals, a superset of october, are on the left. Also,
count(*) is my learned convention instead of count(1).

A simple conditional (filter) count would be much easier to understand and
should be much faster:

Select type, count(*) as total_count, count(*) filter (where month=10) as
m10_count from v_normalized_data group by type;

https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES

I added v_normalized because the expressions the decompose your data tend
to be better placed in a view and your main queries focus just on their
purpose and not structural data manipulation. Especially something
expensive like duplicating split_part.

David J.