DB2 SQL count into multiple columns

Question

i have a situation similar to this: Sql - count into multiple columns

i'll break it down again here

i have a table with these values:

 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. so i would get:

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

the top solution on that question was:

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

when i try this, i get an error saying

An unexpected token "=" was found following "LECT NUM, SUM(type". Expected tokens may include: "CONCAT". SQLSTATE=42601

can somebody point me towards a workable sql query for db2?

Taryn · Accepted Answer · 2013-02-21 20:47:41Z

1

The syntax in the other answer was for MySQL, you should try the following which uses the CASE syntax:

select num,
  sum(case when type = 1 then 1 else 0 end) Type1,
  sum(case when type = 2 then 1 else 0 end) Type2,
  sum(case when type = 3 then 1 else 0 end) Type3
from yourtable
group by num

See SQL Fiddle with Demo (SQL Server demo)

answered Feb 21, 2013 at 20:47

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Collectives™ on Stack Overflow

DB2 SQL count into multiple columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related