TSQL - 选择前10显示其余部分“其他”

Question

我有浏览器的表“名称” S &“版本” S

Name Version 
Safari 5.1 
Safari 4.0 
IE  5.0 
IEMob 9.0 

我正在写一个查询返回十大最常见的浏览器允许和标记任何额外的浏览器为'其他'

这是我的存储过程到目前为止，它返回前10名浏览器像我想要的并创建一个'其他'行，但没有填充它。

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 

如果你能看到我做错了什么，以及如何解决它，请告诉。这些是我从一张大桌子上得到的结果。其他的null版本是有意为之的，因为我正在用这些数据制作饼图，并且希望将“Other”作为单个切片。

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 

Answer 1

尝试改变

 GROUP BY 
     Name 

到

 GROUP BY 
     Name, 
     [Version] 

在联盟的第二部分中的所有