Retrieving Top 10 rows and sum all others in row 11

Question

I have the following query that retrieve the number of users per country;

SELECT C.CountryID AS CountryID, 
       C.CountryName AS Country, 
       Count(FirstName) AS Origin
FROM Users AS U
INNER JOIN Country AS C ON C.CountryID = U.CountryOfOrgin
GROUP BY CASE C.CountryName, 
              C.CountryID

What I need is a way to get the top 10 and then sum all other users in a single row. I know how to get the top 10 but I`m stuck on getting the remaining in a single row. Is there a simple way to do it?

For example if the above query returns 17 records the top ten are displayed and a sum of the users from the 7 remaining country should appear on row 11. On that row 11 the countryid would be 0 and countryname Others

Thanks for your help!

Answer 1

You did not specify how you are ranking the top 10 so I'm assuming the highest counts are ranked higher?

With TopItems As
    (
    SELECT C.CountryID AS CountryID
            , C.CountryName AS Country
            , Count(FirstName) AS Origin
            , ROW_NUMBER() OVER( ORDER BY Count(FirstName) DESC ) As Num
    FROM Users AS U
        JOIN Country AS C 
            ON C.CountryID = U.CountryOfOrgin
    GROUP BY C.CountryName, C.CountryID
    )
Select CountryId, Country, Origin
From TopItems
Where Num <= 10
Union ALL
Select 0, 'Others', Sum(Origin)
From TopItems
Where Num > 10