CODEWITHSUNDEEP
sqlsql-server
dk96m
dk96m

Reputation: 331

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.

Upvotes: 1

Views: 583

Answers (3)

sagi
sagi

Reputation: 40471

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

SELECT COALESCE(t1.JAN, t2.FEB), t1.JAN_cnt, t2.FEB_cnt
FROM
(
    SELECT JAN, COUNT(*) AS JAN_cnt
    FROM yourTable
    GROUP BY JAN
) t1
FULL OUTER JOIN
(
    SELECT FEB, COUNT(*) AS FEB_cnt
    FROM yourTable
    GROUP BY FEB
) t2
    ON t1.JAN = t2.FEB

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would advise putting the values into a different format:

  • opt
  • month
  • cnt

You can do this as:

select opt, mon, count(*) as cnt
from ((select jan as opt, 'jan' as mon from t) union all
      (select feb as opt, 'feb' as mon from t)
     ) o
group by opt, mon;

It is easy enough to switch this to your format:

select opt, sum(jan) as jan, sum(feb) as feb
from ((select jan as opt, 1 as jan, 0 as feb from t) union all
      (select feb as opt, 0, 1, from t)
     ) o
group by opt;

I just prefer the first format. It is easier to generalize to more columns.

Upvotes: 0

Related Questions