CODEWITHSUNDEEP
mysqlsqlsumalias
John Memb
John Memb

Reputation: 23

MySql SUM ALIAS

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)

Upvotes: 2

Views: 11551

Answers (3)

Ali Amjid
Ali Amjid

Reputation: 51

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

Upvotes: 0

mee
mee

Reputation: 332

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

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

To 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

Upvotes: 1

Related Questions