CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
Beems
Beems

Reputation: 810

Count TOP 5, Count all else as "Other"

I have a table of PCs and would like to get a count of the top PC manufacturers. Since the data will be displayed in a pie chart and there are currently ~45 different manufacturers, I'd like to get the count of the top 5, then combine everything else into 'Other'.

The following query will return a count of all PCs by manufacturer:

SELECT 
    f_assetmanufacturer,
    COUNT(*) as 'PCs'
FROM tb_assets 
GROUP BY f_assetmanufacturer

Sample output of the query above is:

f_assetmanufacturer    PCs
-----------------------------
Dell                   100
HP                     50
Lenovo                 25
Acer                   24
Toshiba                23
Microsoft              20
Equus                  20
Thinix                 20
Advantech              20

Desired output would be as follows:

f_assetmanufacturer    PCs                   f_assetmanufacturer    PCs
-----------------------------                -----------------------------
Dell                   100                   Dell                   100
HP                     50                    Other                  100
Lenovo                 25          or        HP                     50
Acer                   24                    Lenovo                 25    
Toshiba                23                    Acer                   24
Other                  100                   Toshiba                23

How can I select only the Top 5 manufacturers and combine all remaining manufacturers into an 'Other' category in order to simplify the output in a pie chart?

Upvotes: 1

Views: 276

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do this with two levels of aggregation:

SELECT (CASE WHEN seqnum <= 5 THEN f_assetmanufacturer ELSE 'Other' END) as assetmanufacturer,
       SUM(PCs) as PCs
FROM (SELECT f_assetmanufacturer, COUNT(*) as PCs,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM tb_assets 
      GROUP BY f_assetmanufacturer
     ) a
GROUP BY (CASE WHEN seqnum <= 5 THEN f_assetmanufacturer ELSE 'Other' END)
ORDER BY PCs DESC;

Note: You might want to use rank() or dense_rank(), depending on how you want to handle ties in the "fifth" place.

Upvotes: 5

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You could use CTEs to build the two sets and union the data together like below:

with top5
as (
SELECT top 5
    f_assetmanufacturer,
    COUNT(*) as 'PCs'
FROM tb_assets 
GROUP BY f_assetmanufacturer
order by 2 desc)
,other
as(
select 'Other',
    COUNT(*) as 'PCs'
FROM tb_assets a
where not exists (select 'ne' from top5 t where a.f_assetmanufacturer = t.f_assetmanufacturer)))

select * from top5
union all
select * from other

Upvotes: 1

Related Questions