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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close