TSQL - Select Top 10 & display the rest as 'Ot

问题:

I have a table of browser "Name"s & "Version"s

Name    Version
Safari  5.1
Safari  4.0
IE      5.0
IEMob   9.0

I'm writing a query to return the Top 10 most common browsers allowed and marking any additional browsers as 'Other'

Here is my stored procedure so far at the moment it returns the top 10 browsers like I wanted and creates an 'Other' row but doesn't populate it.

ALTER PROCEDURE [dbo].[GetUncommonBrowserCount]
    @StartDate datetime = NULL,
    @EndDate datetime = NULL,
    @Domain varchar(255) = NULL
AS
BEGIN

    SET NOCOUNT ON;

UPDATE dbo.Browsers SET [Name] = 'IE' WHERE [Name] = 'InternetExplorer'  

SELECT Name, Count, [Version] FROM
(
    SELECT TOP 10 
        Name,
        COUNT(Name) AS [Count],
        [Version]
    FROM 
        dbo.GetCommonBrowserAccessEvents
    WHERE 
        NOT
        (   
            Name = 'Chrome' OR [Name] = 'Firefox' OR Name = 'IE' AND CAST(SUBSTRING([Version], 1, CHARINDEX('.', [Version]) - 1) AS INT) > 7
        )
        AND
        (
            @StartDate IS NULL OR [Date] > @StartDate
        )
        AND
        (
            @EndDate IS NULL OR [Date] < @EndDate
        )
        AND
        (
            @Domain IS NULL OR DomainName = @Domain
        )
        GROUP BY 
            Name,
            [Version]
        ORDER BY 
            COUNT(Name) DESC
) AS A1

    UNION ALL

    SELECT
        'Other' AS Name,
        COUNT(Name) AS [Count],
        [Version] = NULL
    FROM 
        dbo.GetCommonBrowserAccessEvents
    WHERE
        Name NOT IN (SELECT TOP 10
            Name
        FROM
            dbo.GetCommonBrowserAccessEvents
        GROUP BY 
            Name
        ORDER BY 
            COUNT(Name) DESC)
    END

If you can see what I'm doing wrong and how to fix it please tell. These are the results I get from a large table. The null version for other is intentional as I'm making a pie chart with this data and want 'Other' as a single slice.

Name  Count Version
Safari  46  5.1
Mozilla 18  0.0
Safari  16  5.0
IE      12  7.0
Safari  12  0.0
Safari  9   6.0
IEMob   8   9.0
IEMob   8   10.0
IE      8   6.0
IE      1   3.2A
Other   0   NULL

回答1:

Try changing

        GROUP BY 
        Name

to

        GROUP BY 
        Name,
        [Version]

in the second part of your UNION ALL