TSQL adding "Other" to a GROUP by Percentage Query

How would I construct a query that would classify anything under a certain p开发者_开发知识库ercentage as Other?

For example with:

Select Country, (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc
FROM Logs
Group by Country
HAVING (COUNT(Country)*100 / (Select COUNT(*) From Logs)) > 5
ORDER BY Perc DESC

How would I add a row "Other" that would the sum of everything less than 6 percent?

Another approach

;WITH Logs(Country) AS
(
SELECT TOP 10 'UK' FROM sys.objects UNION ALL
SELECT TOP 10 'US' FROM sys.objects UNION ALL
SELECT TOP 1 'Timbuktu' FROM sys.objects
),
GroupedLogs AS
(
SELECT Country, 
      (COUNT(Country) * 100.0 / (SELECT COUNT(*) FROM Logs)) AS Perc
FROM Logs
GROUP BY Country
)
SELECT CASE WHEN Perc<6 THEN 'Other' ELSE Country END AS Country, 
       SUM(Perc) AS Perc
FROM GroupedLogs
GROUP BY CASE WHEN Perc<6 THEN 'Other' ELSE Country END

I think you need a UNION for this. Not tested, but something like:

SELECT * FROM 
(
    Select 
        Country, 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc 
    FROM 
        Logs 
    Group by 
        Country 
    HAVING 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) >= 6    

    UNION

    Select 
        'Other' as Country, 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) as Perc 
    FROM 
        Logs 
    HAVING 
        (COUNT(Country)*100 / (Select COUNT(*) From Logs)) < 6 
)
ORDER BY 
    Perc DESC

Or try something on the lines of below. Here the percentages are calculated in the CTE:

DECLARE @countries TABLE (name varchar(16), hit INT);


INSERT INTO @countries
VALUES
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('UK',1),
('FRA',1),
('FRA',1),
('FRA',1),
('FRA',1),
('FRA',1),
('USA',1),
('MEX',1);

WITH MY_PERCENTAGES AS
(
    SELECT name, 
                CAST(count(*) AS NUMERIC(5,2)) 
                / 
                CAST(SUM(count(*)) OVER() AS NUMERIC(5,2)) * 100.
             AS percentage
    FROM @countries
    GROUP BY name
)
SELECT
    name,
    SUM (percentage) AS percentages
FROM 
(
    SELECT 
        CASE 
        WHEN percentage <6 THEN
            'OTHER'
        ELSE
            name
        END AS name,
        percentage
    FROM MY_PERCENTAGES
) TMP
GROUP BY name;