CODEWITHSUNDEEP
sqldb2
Chad
Chad

Reputation: 3

Summing Counts into Multiple New Columns Grouped By Another Column

I am trying to subtract two columns and then count how many have the same difference and put those sums into columns. The sums are of how many have a difference of -3 or more, -2, -1, 0, 1, 2, 3 or more grouped by date.

Query must be executed against a DB2 database.

Data...

------------------------------
| Date       | Num 1 | Num 2 |
------------------------------
| 2014-02-11 | 19872 | 19873 |
| 2014-02-11 | 19873 | 19873 |
| 2014-02-12 | 19875 | 19873 |
| 2014-02-13 | 19870 | 19873 |
| 2014-02-13 | 19872 | 19873 |
| 2014-02-14 | 19877 | 19869 |
| 2014-02-14 | 19873 | 19873 |

Desired Output...

-----------------------------------------------------------------------
| Date        | <= -3 |  -2   |  -1   |   0   |  +1   |  +2   | >= +3 |
-----------------------------------------------------------------------
| 2014-02-11  |     0 |   0   |   0   |   1   |   1   |   0   |    0  |
| 2014-02-12  |     0 |   1   |   0   |   0   |   0   |   0   |    0  |
| 2014-02-13  |     0 |   0   |   0   |   0   |   1   |   0   |    1  |
| 2014-02-14  |     1 |   0   |   0   |   1   |   9   |   0   |    0  |

Upvotes: 0

Views: 81

Answers (1)

SouravA
SouravA

Reputation: 5243

Try this: 

select Date, 
sum(case when diff <= -3 then 1 else 0 ) AS [<=-3], 
sum(case when diff = -2 then 1 else 0 ) AS [-2], 
sum(case when diff = -1 then 1 else 0 ) AS [-1], 
sum(case when diff = 0 then 1 else 0 ) AS [0], 
sum(case when diff = 1 then 1 else 0 ) AS [+1], 
sum(case when diff = 2 then 1 else 0 ) AS [+2], 
sum(case when diff >= 3 then 1 else 0 ) AS [>=+3]
from 
(select Date, Num1, Num2, (Num1-Num2) diff from TableA)TableB
group by Date

Upvotes: 2

Related Questions