CODEWITHSUNDEEP
mysql
Bingo
Bingo

Reputation: 29

Mysql top5 and sum remaining as others

Basically I am trying to sum the values of all quarters by companies and group the remaining in others in one sql query. I have seen some examples here but I can't get it working.

This is my query:

SELECT name, ifnull(q1_2014,0) + ifnull(q2_2014,0) + 
ifnull(q3_2014,0) + ifnull(q4_2014) as yearly

select (CASE    

WHEN @curRank < 5 THEN yearly ELSE 'others' 
                END AS yearly)

group others
where country ='Russia';

My table is

Country name    q1_2014 q2_2014 q3_2014 q4_2014
Russia  CompanyA    6   6   6   6
Russia  CompanyB    5   5   5   5
Russia  CompanyC    4   4   4   4
Russia  CompanyD    3   3   3   3
Russia  CompanyE    2   2   2   2
Russia  CompanyF    1   1   1   1
Russia  CompanyG    1   1   1   1
Russia  CompanyH    1   1   1   1
Russia  CompanyI    1   1   1   1
Russia  CompanyJ    1   1   1   1
Russia  CompanyK    1   1   1   1

The expected result should be the sum of a company grouped as yearly of the top 5 and remaining the sum grouped in others.

CompanyA    24
CompanyB    20
CompanyC    16
CompanyD    12
CompanyE    8
Others  24

What am I doing wrong?

Upvotes: 0

Views: 155

Answers (1)

Prasanth V J
Prasanth V J

Reputation: 1196

Check this

select IF(rank <=5,name,'others') as Company,sum(yearly) from( SELECT  `name`, IFNULL(`q1_2014`,0)+IFNULL( `q2_2014`,0)+ IFNULL(`q3_2014`,0)+ IFNULL(`q4_2014`,0) as yearly,
@curRank := @curRank + 1 AS rank
from Table1,(SELECT @curRank := 0) r   where country='Russia' ORDER BY yearly desc) as Table2 group by company

DEMO

Upvotes: 1

Related Questions