How to query total sum of sales by month by teams?

Question

What is the best way to get the best selling teams by each month when I have tables like these:

The results should be something like this (group by total price of orders):

Month | Team | Sales
____________________
March |   2  | 3453 
April |   3  | 1353 
May   |   2  | 5341

I have joined two tables before but for some reason joining 4 tables and grouping them by month seems difficult. Thank you.

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Dec 27 '19 at 23:06

score 1 · Answer 1 · answered Dec 27 '19 at 22:39

This should do it. I added the year too. It uses a CTE

with cte as
(
    select Team_nr, sum(price) as Sales, date_part('month', Created) as _Month, date_part('year', Created) as _Year
    from employee e 
    inner join orders o on e.id = o.employee_ID
    inner join products p on p.id = orders.product_id 
    Group by Team_nr, date_part('month', Created), date_part('year', Created)
)
select Team_nr, Sales, _Month, _Year
from cte a
where not exists(select 1 from cte b where 
a._Month = b._Month and a._Year = b._Year and a.Team_nr <> b.Team_nr and a.Sales < b.Sales  )

score 1 · Accepted Answer · answered Dec 27 '19 at 23:00

In Postgres, you can use distinct on -- if you want exactly one row per month:

select date_trunc('month', created), e.Team_nr, sum(p.price) as Sales
from employee e  join
     orders o
     on e.id = o.employee_ID join
     products p
     on p.id = orders.product_id 
group by e.Team_nr, date_trunc('month', o.Created)
order by date_trunc('month', o.Created), sum(p.price) desc

How to query total sum of sales by month by teams?

2 Answers2