I have reports table that aggregates data each product quantity each day.
SELECT r.year, r.month, c.id, c.client_name, p.product_name, cc.country_name, sum(r.quantity) units FROM
client c
join report r on c.id = r.client_id
join product p on r.product_id = p.id
join country cc on r.country_id = c.id
WHERE r.year = year(now())
group by r.year, r.month, c.id, p.product_name, cc.country_name
I'm trying to figure out how group units sum by month, client, product and country where query shows sum for top 5 countries and rest is sum from bottom countries. Something like this:
case
when sum(r.quantity) = 'Top 1' then cc.country_name
when sum(r.quantity) = Top 2' then cc.country_name
.....
when sum(r.quantity) = 'Top 2' then cc.country_name
else 'Other'
How can I do this?
Many thanks in advance
Aucun commentaire:
Enregistrer un commentaire