vendredi 1 juillet 2016

MySQL - How to group sum top 6 and the rest

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