dk96m dk96m - 5 months ago 20
SQL Question

MSSQL Count Multiple Columns

Say I have a table like this in ms sql 2008:

+------+--------+---------+
| year | JAN | FEB |
+------+--------+---------+
| 2016 | 5K2 | 5K2 |
| 2016 | 5K2 | 5K2 |
| 2016 | 5K2 | 5K2 |
| 2016 | 8Z | 8Z |
| 2016 | R5205 | R5205 |
| 2016 | 5K2 | 5K2 |
| 2016 | 5K2 | 5K2 |
| 2016 | NULL | NULL |
| 2016 | TE | NULL |
| 2016 | TE | NULL |
| 2016 | 8Z | 8Z |
+------+--------+---------+


And I want to get a count for each column, something like this

+------+--------+---------+
| opt | JAN_cnt| FEB_cnt |
+------+--------+---------+
| 5K2 | 5 | 4 |
| 8Z | 2 | 2 |
| R5205| 1 | 1 |
| TE | 2 | 0 |
| NULL | 1 | 4 |
+------+--------+---------+


First, can this be done? Second, how? I have searched, but cant find exactly what I am looking for.

Answer

I think the simplest way is to use UNION ALL with conditional aggregation using CASE EXPRESSION :

SELECT s.opt,   
       COUNT(CASE WHEN s.ind_from = 1 THEN 1 END) as jan_cnt,
       COUNT(CASE WHEN s.ind_from = 2 THEN 1 END) as feb_cnt
FROM (
    SELECT t1.jan as opt,1 as ind_from FROM YourTable t1
    UNION ALL 
    SELECT t2.feb,2 FROM YourTable t2) s
GROUP BY s.opt