0

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  |
Chad
  • 3
  • 2
  • Did you try anything? – bfontaine Sep 30 '14 at 16:24
  • Ultimately, you are looking for a pivot: http://stackoverflow.com/questions/13579143/how-can-i-pivot-a-table-in-db2 –  Sep 30 '14 at 16:30
  • @DanK So, it's a **Pivot**? That's been half of the problem. I just don't know what to properly call what I am trying to do. So, that makes Googling it rather difficult. Thanks! – Chad Sep 30 '14 at 19:23

1 Answers1

2

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
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • Depending on how the optimizer handles it, this may run better if you aggregate before the pivot (that is, nest another level). At minimum, you don't actually need to pull up `Num1`/`Num2` into the next level. – Clockwork-Muse Oct 04 '14 at 07:26