GROUP BY multiple columns
My table structure is empid, category, col1, col2, amount. I would like to get the results as total of amounts for the col1 and col2 combinations, with a particular category.
Hi, my table structure is empid, category, col1, col2, amount. I have five records in this way:
empid category col1 col2 amount 1 categoryA 1 10 2.5 1 categoryB 1 10 3.4 1 categoryC 2 11 1.1 1 categoryD 2 11 1.2 1 categoryE 2 11 1.3
I would like to get the results as total of amounts for the col1 and col2 combinations, with a particular category, like this:
empid category total 1 categoryA 5.9 (=2.5+3.4) 1 categoryD 3.6 (=1.1+1.2+1.3)
The values categoryA and categoryD are predefined, which I should retrieve. Could you please help me with how to do that?
Since you want totals for every combination of col1 and col2, the query involves a multiple-column GROUP BY:
select empid
, something as category
, sum(amount) as total
from yourtable
group
by empid
, col1
, col2
The only thing left to figure out is how to pick the correct value of the category column from within each empid/col1/col2 group. What does "predefined" mean? Does each empid/col1/col2 have to have one of those two values?
If so, this might be one way to accomplish it:
select empid
, max(case when category
in ('categoryA','categoryD')
then category
else null end) as category
, sum(amount) as total
from yourtable
group
by empid
, col1
, col2
having sum(case when category
in ('categoryA','categoryD')
then 1
else 0 end) > 0
If that's not what you want, do please ask a further question.
