SUM OVER PARTITION and GROUP BY in one pass?

Question

My question is similar to this but the answer is not what I want:

Cannot use group by and over(partition by) in the same query?

My table is like this:

CODE_1  CODE_2  AMOUNT
A       A.1     2
A               4
A               6
B               1
B       B.1     3
B       B.1     5

I want to get the total of Code_1 and [Code_1, Code_2] like this:

CODE_1  CODE_2  Total_1 Total_2
A       A1      12      2
B       B1      9       8

Can I get the result in one SELECT? Thanks!

What database are you using? DB2? Please add the appropriate tag. — 200_success, Nov 11 '15 at 21:43
Use a derived table or CTE. Do the grouping in the inner table. — shawnt00, Nov 11 '15 at 21:45
Can there be different `CODE_2` values for a given `CODE_1`? If so, what should that look like in your output? — Matthew McPeak, Nov 11 '15 at 21:49
Since we only have NULL and one value in Code_2 column for a given Code_1, I almost forgot this condition. But there maybe more values in Code_2 in the future. Thank you for reminding me. — abacusit, Nov 12 '15 at 13:15

score 3 · Answer 1 · edited Nov 11 '15 at 21:54

3

Is this what you're looking for? It's hard to know how you intend to treat the CODE_2 data:

select
    code_1, 
    max(CODE_2) as CODE_2,
    sum(AMOUNT) as Total_1,
    sum(case when CODE_2 is not null then AMOUNT end) as Total_2
from T
    group by CODE_1

edited Nov 11 '15 at 21:54

Hogan

69,564
10
76
117

answered Nov 11 '15 at 21:49

shawnt00

16,443
3
17
22

SUM OVER PARTITION and GROUP BY in one pass?

1 Answers1