Count aggregate with a separate row for other-CodePudding

I'm trying to perform a groupby count aggregate on a dataset with a list of attendees. I would like to count by CompanyType column. I only want to include those CompanyTypes with more than 50 attendees, with the rest in a single 'Other' row. Here is my code;

SELECT CompanyType, 
Count(*) AS Count
FROM Attendence
WHERE CompanyType IS NOT NULL
Group by CompanyType
ORDER BY 2 DESC

Thanks in advance

CodePudding user response：

Use this:

WITH tbl AS (
            SELECT CompanyType, 
                       Count(*) AS Count
                FROM Attendence
                WHERE CompanyType IS NOT NULL 
                Group by CompanyType
)
select CompanyType,Count from tbl where Count>50
Union all
select 'Other' as CompanyType,sum(Count) as Count  from tbl where Count<=50

Items above 50 are selected in a query, and items below 50 are all aggregated and presented as a record.

CodePudding user response：

Try the following scripts

-- Get the result with more than 50 attendees
SELECT CompanyType, 
Count(*) AS Count
FROM Attendence
WHERE CompanyType IS NOT NULL
Group by CompanyType 
HAVING COUNT(*)>50
ORDER BY 2 DESC

-- To get the 'Other' in single row along with CompanyTypes with more than 50 attendees

;WITH CTE AS (
            SELECT CompanyType, 
            COUNT(*) AS Total
            FROM Attendence
            WHERE CompanyType IS NOT NULL
            Group by CompanyType )
SELECT CompanyType,Total 
FROM CTE 
WHERE Total>50

Union 

SELECT 'Other' as CompanyType1,sum(Total) as Total 
FROM CTE 
WHERE Total<=50 
GROUP BY CompanyType1;

CTE: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

HAVING: https://www.w3schools.com/sql/sql_having.asp