Mysql query - Issue

Question

I am facing 1 issue with MySql query:

select sum(h.total_rooms) as total_rooms from reservation_details as rd LEFT JOIN hotels as h ON rd.hotel_id = h.id left join `chains` as `c` on `c`.`id` = `h`.`chain_id` where YEAR(rd.created_at) = 2016 and MONTH(rd.created_at) = 5 and rd.status >= 50 and h.chain_id = 2 GROUP BY rd.hotel_id

Above query returns :

total_rooms
48216
7700
13250

But I need sum of the query.

Possible duplicate of [MySql sum elements of a column](http://stackoverflow.com/questions/4586040/mysql-sum-elements-of-a-column) — Al-Mustafa Azhari, Apr 12 '17 at 12:06

Satish Saini · Answer 1 · 2017-04-12T14:02:05.960

0

Try to use sum() function like this and use ROLLUP with your query.

select sum(h.total_rooms) as total_rooms from reservation_details as rd 
LEFT JOIN hotels as h ON rd.hotel_id = h.id 
left join `chains` as `c` on `c`.`id` = `h`.`chain_id` 
where YEAR(rd.created_at) = 2016 and MONTH(rd.created_at) = 5 
and rd.status >= 50 and h.chain_id = 2 GROUP BY rd.hotel_id 
WITH ROLLUP

edited Apr 12 '17 at 14:02

answered Apr 12 '17 at 12:04

Satish Saini

2,880
3
24
38

I already did @Satish Saini. But no luck, it showing wrong data – Rakesh Tripathi Apr 12 '17 at 12:14
@RakeshTripathi What do you mean wrong data? If above rows are coming in response and we need to sum it then this function should work fine. – Satish Saini Apr 12 '17 at 12:15
Using SUM() returns me total_rooms 48216
7700
13250 – Rakesh Tripathi Apr 12 '17 at 12:16
@RakeshTripathi Check my updated answer. You can use Rollup with your query. Let me know if that works for you. – Satish Saini Apr 12 '17 at 14:02
Nope its not working, it was throwing same result as above. @Satish Saini – Rakesh Tripathi Apr 13 '17 at 04:54
@RakeshTripathi can you please email me your table dump so I can check it here at my end? – Satish Saini Apr 13 '17 at 06:35

Mysql query - Issue

1 Answers1