How can I summarize rows that occur only once?

Question

I have a query which returns the number of rows of a distinct device_type which occur more than once.

SELECT COUNT(*) AS C1,device_type FROM stat 
    WHERE stat_date = '2012-02-08' 
    GROUP BY 2 HAVING C1 > 1 
    ORDER BY 1 DESC

I would like to summarize the remaining (HAVING count = 1) rows as 'others'

How can I add the sum of COUNT(*) and 'others' as second column for the following query?

SELECT COUNT(*) AS C2,device_type FROM stat 
    WHERE stat_date = '2012-02-08' 
    GROUP BY 2 HAVING C2 = 1 
    ORDER BY 1 DESC

Sample data in DB

device_type
dt1
dt1
dt1
dt2
dt2
dt3
dt4
dt5

expected result

3 dt1
2 dt2
3 other

Answer 1

I would do this.

SELECT COUNT(*) AS C1,device_type FROM stat 
    WHERE stat_date = '2012-02-08' 
    GROUP BY 2 HAVING C1 > 1 
    ORDER BY 1 DESC
Union

SELECT Sum(1),'OTHERS'FROM stat 
    WHERE stat_date = '2012-02-08' 
    GROUP BY 2 HAVING C1 =1
    ORDER BY 1 DESC

Answer 2

You can also try:

SELECT SUM(C1) AS C1, CASE WHEN C1 = 1 THEN 'other' ELSE device_type END as device_type
FROM (  SELECT  COUNT(*) AS C1,
                device_type 
        FROM stat 
        WHERE stat_date = '2012-02-08' 
        GROUP BY device_type) A
GROUP BY CASE WHEN C1 = 1 THEN 'other' ELSE device_type END