SQL Grouping and counting together

Question

I try to get the grouping elements as column and the count as row elements.

Possible Status = IO, NIO, OPEN, CLOSED, CANCELLED
Possible Types = TypeA, TypeB, typeC, typeD

MyTable (date, status, type, count)

2014-06-27,OPEN,typeA, 1
2014-07-01,OPEN,typeA, 1
2014-07-02,IO,typeB, 2
2014-07-02,IO,typeC, 3
2014-07-02,NIO,typeC, 4

The result should look like. (Groupping on date and status)

date,status,typeA_Count, typeB_Count, typeC_Count, typeD_Count

2014-06-27,OPEN,1, 0, 0, 0
2014-07-01,OPEN,1, 0, 0, 0
2014-07-02,IO, 0, 2, 3, 0
2014-07-02,NIO, 0, 0, 4, 0

`DB2 database`? Which one? That is, how old or new is it? (What release?) And what platform is it installed on? (Different platforms have their own special capabilities or restrictions.) — user2338816, Sep 27 '14 at 09:42
This is a bog-standard [Pivot Query](http://stackoverflow.com/questions/4677287/convert-row-to-column). Beyond that, you should _really_ avoid using reserved words (like `date`, or `count`) for identifiers. For one thing, in the case of `date`, it doesn't really even mean anything. Date of what? Creation of the row, modified, your mother's birthday? Be more specific, so future maintainers (like yourself) will thank you. — Clockwork-Muse, Sep 28 '14 at 12:18

score 3 · Accepted Answer · answered Sep 26 '14 at 10:40

A simple way is using CASE/GROUP BY to add up the values;

SELECT "date", "status",
  SUM(CASE WHEN "type"='typeA' THEN "count" ELSE 0 END) typeA_count,
  SUM(CASE WHEN "type"='typeB' THEN "count" ELSE 0 END) typeB_count,
  SUM(CASE WHEN "type"='typeC' THEN "count" ELSE 0 END) typeC_count,
  SUM(CASE WHEN "type"='typeD' THEN "count" ELSE 0 END) typeD_count
FROM mytable
GROUP BY "date", "status"
ORDER BY "date", "status"

An Oracle SQLfiddle to test with, although DB2 should run the same without problems.

score 0 · Answer 2 · answered Sep 26 '14 at 10:48

DB2 doesn't support the PIVOT clause therefore try this:

select date
     , status
     , sum(decode(type,'typeA',count,0)) as count_typeA
     , sum(decode(type,'typeB',count,0)) as count_typeB
     , sum(decode(type,'typeC',count,0)) as count_typeC
     , sum(decode(type,'typeD',count,0)) as count_typeD
  from mytable
 group by date, status

SQL Grouping and counting together

2 Answers2