Sql - count into multiple columns

Question

I have a table like this:

 num | type
-----+------
 123 | 3
 123 | 2
 123 | 3
 123 | 2
 124 | 3
 124 | 1
 124 | 3
 124 | 3

I want to group by the num column, and have a column counting each distinct type (I know all the possible values here in advance). So I would get:

 num | 1 | 2 | 3
-----+---+---+---
 123 | 0 | 2 | 2
 124 | 1 | 0 | 3

Is this possible with SQL? I am using MySql.

Praveen Kumar Purushothaman · Accepted Answer · 2012-06-28 20:39:26Z

7

SELECT `num`,
    SUM(type = 1) as `1`, 
    SUM(type = 2) as `2`, 
    SUM(type = 3) as `3`
FROM `your_table`
GROUP BY `num`

edited Jun 28, 2012 at 20:39

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

answered Jun 28, 2012 at 8:01

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andomar Over a year ago

That's certainly more concise than sum(case when type = 1 then 1 end) ! +1

MatBailie Over a year ago

@Andomar - It certainly is. Although I can't express a strong/clear reason, I actually don't like using that type of behaviour. I'd up-vote SUM(IF(type=1, 1, 0)) though. Does anyone else have an opinion on whether SUM(type=1) is better or worse than SUM(IF(type =1, 1, 0))?

juergen d Over a year ago

@Dems: I like the short version better. But it is good to have an alternative mentioned in your comment.

Collectives™ on Stack Overflow

Sql - count into multiple columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related