I am trying to do up a query for a table hotel, roomtype, roomprice, I am trying to get sum of roomprice as "total Single Rooms" where roomtype = single grouped by hotel So I end up with hotel and "Total Single rooms"
I have been trying different ways but I can not seem to get that to work. Yes I am new to SQL lol
Thanks
You should accept MrVimes solution if it meets your requirement. If you want to include a hotel chain total you could use a with rollup to the group by for example:-
/*drop table t;
create table t (hotel varchar(3),roomtype varchar(1),price decimal(10,2));
truncate table t;
insert into t values
('abc','s',10.00),
('abc','s',10.00),
('abc','s',30.00),
('abc','d',10.00),
('def','s',10.00),
('def','s',10.00),
('abc','d',10.00),
('abc','d',10.00),
('abc','d',10.00);
*/
select case
when s.hotel is not null then s.hotel
else 'Total'
end as hotel,
s.totalsinglerooms
from
(
select hotel, sum(price) as totalsinglerooms
from t
where roomtype = 's'
group by hotel with rollup
) s
result
+-------+------------------+
| hotel | totalsinglerooms |
+-------+------------------+
| abc | 50.00 |
| def | 20.00 |
| Total | 70.00 |
+-------+------------------+
select hotelno,sum(roomprice) as `Total Single Rooms` from room
where roomtype = 'single'
group by hotelno
having sum(roomprice) > 100
