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.

Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

  • How developers can avoid remote work scams

    Software developers can find good remote programming jobs, but some job offers are too good to be true. Follow these tips to spot...

Data Center
Content Management
HRSoftware
Close