CODEWITHSUNDEEP
sqlpostgresql
Jyrto
Jyrto

Reputation: 37

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

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

enter image description here

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.

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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

Upvotes: 1

zip
zip

Reputation: 4061

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  )

Upvotes: 1

Related Questions