Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in January 2007

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close