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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading