Take the 2-minute tour ×

Suppose I've got the following table:

Room | Seats | Occupied
a1      20       10
b2      15       15
c3      45       30
d4      20       15
e5      10       10

I have a trouble (just have no idea how that can be done) generating a SELECT statement that will merge some of the rooms and will sum up their values and will return the following:

Room | Seats | Occupied
a1      20       10
b2c3d4  80       60
e5      10       10

How can this be done?

Thank you in advance!

share|improve this question
How to group them? –  juergen d Nov 5 '12 at 17:57
It should "merge" the rows as I showed in the question. –  user1751343 Nov 5 '12 at 18:13
And by what rule? –  juergen d Nov 5 '12 at 18:14
There is no rule. Suppose that the values for the Room to be grouped will always be b2, c3 and d4. Thanks. –  user1751343 Nov 5 '12 at 18:16
add comment

closed as too localized by juergen d, WhozCraig, Tom Seidel, fancyPants, Mudassir Nov 6 '12 at 10:11


1 Answer

up vote 1 down vote accepted
select room,
       sum(seats) as seats,
       sum(occupied) as occupied
from your_table
group by case when room in ('b2', 'c3', 'd4') 
              then 'b2c3d4'
              else room
share|improve this answer
Thank you very much! –  user1751343 Nov 5 '12 at 18:22
add comment
