Sql Query to get total rows and total rows matching specific condition

Question

OK, Here is what my table looks like

------------------------------------------------
id                     type
-----------------------------------------------
1                      a
2                      b
3                      a
4                      c
5                      c
7                      a
8                      a
------------------------------------------------

Now, I need a query that can give me this output...

-----------------------------------------------------------------
count(*)   |   count(type=a)   | count(type=b)  | count(type=c)
-----------------------------------------------------------------
8                  4                 1               3
------------------------------------------------------------------

I only know to get the total set using count(*), but how to do the remaining

Answer 1

SELECT 
COUNT(*),
COUNT(CASE WHEN type='a' THEN 1 END) as count_a,
COUNT(CASE WHEN type='b' THEN 1 END) as count_b,
COUNT(CASE WHEN type='c' THEN 1 END) as count_c,
FROM table1;

//or you can do 
SELECT type, COUNT(*) 
FROM table1 
GROUP BY type WITH ROLLUP

In the latter case you will get results :

a | 4
b | 1
c | 3
null | 8

Answer 2

You could try something like

SELECT  COUNT(*) Total, 
        SUM(CASE WHEN type='a' THEN 1 ELSE 0 END) as CountOfA, 
        SUM(CASE WHEN type='b' THEN 1 ELSE 0 END) as CountOfB, 
        SUM(CASE WHEN type='c' THEN 1 ELSE 0 END) as CountOfC, 
FROM    Table