CODEWITHSUNDEEP
mysql
Dmitry
Dmitry

Reputation: 505

MySQL GROUP BY - get SUM of few grouped values

I have a simple db where I have users and every user have 'country', for ex:

Dmitry - US
Ann - US
John - UK
Roman - Japan
Mila - China
Jane - Australia

I want to get count of very country users, BUT I need to get TOP 3 countries users counts (US, UK, Japan for example), and all other countries users count should be summarized together as "Rest". How to do this?

So in my example this should give me this result from SQL:

US = 2
UK = 1
Japan = 1
Rest = 2

If I will make regular SQL:

SELECT count(userid) FROM users GROUP BY country

I will get results for every country, but I need only TOP 3 and all others count as "Rest" in results. Thanks!

P.S.: I tried to create SQLFiddle for this, but their website is down right now and I can't use it.

Upvotes: 0

Views: 44

Answers (1)

forpas
forpas

Reputation: 164099

You can group by country and use ROW_NUMBER() window function to rank the countries based on the number of times they appear.
Then add another level of aggregation based on the ranking position of each country:

SELECT CASE WHEN rn <= 3 THEN country ELSE 'Rest' END country,
       SUM(counter) counter
FROM (
  SELECT country, COUNT(*) counter,
         ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rn
  FROM users
  GROUP BY country
) t
GROUP BY 1;

Note that the countries returned as top 3 in case of ties may be arbitrary chosen, so you could add another condition in the ORDER BY clause of ROW_NUMBER(), like:

ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, country)

which would return different but consistent results.

See the demo.

Upvotes: 1

Related Questions