Re: total and partial sums in the same query??
| От | David G. Johnston |
|---|---|
| Тема | Re: total and partial sums in the same query?? |
| Дата | |
| Msg-id | CAKFQuwZK=ESYy3_0Aw66Q5a9zJKdzqye-59+fWBzj=VSED=P=g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: total and partial sums in the same query?? (Iuri Sampaio <iuri.sampaio@gmail.com>) |
| Ответы |
Re: total and partial sums in the same query??
|
| Список | pgsql-sql |
On Friday, October 9, 2020, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
Hi David,RIGHT OUTER JOIN is the key!TOTALSELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM qt_vehicle_ti GROUP BY typeOCTOBERSELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) = 10 GROUP BY typeFINALSELECT 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, partialLet 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;
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.
В списке pgsql-sql по дате отправления: