How can I sum lines that occur only once?

Question

I have a query that returns the number of lines of a single device_type that occurs 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 sum the remaining lines (HAVING count = 1) as "others"

How can I add the sum of COUNT (*) and "others" as the second column for the next 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

Example data in DB

device_type
dt1
dt1
dt1
dt2
dt2
dt3
dt4
dt5

Expected Result

3 dt1
2 dt2
3 other

Answer 1

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

Answer 2

I would do that.

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