Retrieving Top 10 rows and sum all others in row 1

问题:

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!

回答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

回答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.