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 an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
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