Sql Query to get summary rows and summary rows matching a specific condition

Question

Ok that's 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 conclusion ...

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

I only know to get a common set using count (*), but how to make the rest

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 the results:

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

Answer 2

You can 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

Answer 3

select count(*),type
from [TableName]
Group By Type