Retrieving Top 10 Rows And Sum All Others In Row 11

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

Solution 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

Solution 2:

Something like this:

SELECT
    -- show them
    ROW_NUMBER() OVER (ORDER BY CASE WHEN country_code = 'Others' THEN 1 ELSE 0 END, SUM(n) DESC) AS nr,
    countryID,
    SUM(n)
FROM (
    -- change name for some countries
    SELECT
        CASE WHEN nr >= 11 THEN 'Others' ELSE countryID END AS countryID,
        n
    -- select all countries
    FROM ( 
        SELECT 
                    -- store number to recognize position
            ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS nr, 
            countries.countryID,
            COUNT(*) AS n
        FROM
            countries WITH (NOLOCK)
            JOIN
                users WITH (NOLOCK)
            ON
                users.countryID = countries.countryID
        GROUP BY
            countries.countryID
    ) AS x
) AS y
GROUP BY
    countryID
ORDER BY
    -- show Others as last one
    CASE WHEN countryID = 'Others' THEN 1 ELSE 0 END,
    SUM(n) DESC

works for me.