I have a problem with mysql alias.
I have this query:
SELECT (`number_of_rooms`) AS total, id_room_type,
COUNT( fk_room_type ) AS reservation ,
SUM(number_of_rooms - reservation) AS result
FROM room_type
LEFT JOIN room_type_reservation
ON id_room_type = fk_room_type
WHERE result > 10
GROUP BY id_room_type
My problem start from SUM, cannot recognize reservation and then i want to use the result for a where condition. Like (where result > 10)
Not 100% but to the best of my knowledge you cant use aliases in your declarations, and thats why you are getting the column issue. Try this:
SELECT (`number_of_rooms`) AS total, id_room_type,
COUNT( fk_room_type ) AS reservation ,
SUM(number_of_rooms - COUNT( fk_room_type ) ) AS result
FROM room_type
LEFT JOIN room_type_reservation
ON id_room_type = fk_room_type
GROUP BY id_room_type
Having SUM(number_of_rooms - COUNT( fk_room_type ) ) > 10
SELECT ('number_of_rooms') AS total, id_room_type, COUNT( fk_room_type ) AS reservation , SUM(number_of_rooms) - COUNT( fk_room_type ) AS result FROM room_type LEFT JOIN room_type_reservation ON id_room_type = fk_room_type GROUP BY id_room_type Having SUM(number_of_rooms) - COUNT( fk_room_type ) > 10number_of_rooms ) AS total, id_room_type, c.reservation, SUM( number_of_rooms - c.reservation ) AS result FROM room_type LEFT JOIN ( SELECT number_of_rooms AS moutsela, fk_room_type, COUNT( fk_room_type ) AS reservation FROM room_type LEFT JOIN room_type_reservation ON id_room_type = fk_room_type GROUP BY id_room_type )c ON id_room_type = fk_room_type GROUP BY id_room_type LIMIT 0 , 30 but i ll try to find the way to optimize my queryTo apply a predicate (filter condition) on the result of an aggregate function, you use a Having clause. Where clause expressions are only applicable to intermediate result sets created prior to any aggregation.
SELECT (`number_of_rooms`) AS total, id_room_type,
COUNT( fk_room_type ) AS reservation ,
SUM(number_of_rooms - reservation) AS result
FROM room_type
LEFT JOIN room_type_reservation
ON id_room_type = fk_room_type
GROUP BY id_room_type
Having SUM(number_of_rooms - reservation) > 10
One way is to wrap it into one more SELECT
SELECT t.*, t.number_of_rooms - t.reservation AS result FROM
(
SELECT (`number_of_rooms`) AS total, id_room_type,
COUNT( fk_room_type ) AS reservation ,
SUM(number_of_rooms - reservation) AS result
FROM room_type
LEFT JOIN room_type_reservation
ON id_room_type = fk_room_type
WHERE result > 10
GROUP BY id_room_type
) t
