MySQL only returning one row using one table

Question

I want to find the total room cost and total stay cost (room and expenses), and display the ones that are above $50 for total cost.

I wrote this query:

SELECT first_name, last_name, expenses, room_rate,
SUM(stays_num*room_rate) as 'total_room_cost',
SUM((stays_num*room_rate)+expenses) as 'total_stay_cost'
FROM assign1.customer
WHERE 'total_stay_cost' < 50

The only one that pops up is Kristoff Kurn. Justin Hackman should also come up because he has also spent more than 50 in total.

Answer 1

When you select normal columns and aggregate functions, you need to use GROUP BY to tell over which you want the aggregate calculation to be performed.

Secondly, the conditions that affect the aggregate function results, should be put in a HAVING-clause (sort of a where-clause for group by).

So you query would look like this:

SELECT first_name, last_name, expenses, room_rate,
  SUM(stays_num*room_rate) as 'total_room_cost',
  SUM((stays_num*room_rate)+expenses) as 'total_stay_cost'
FROM assign1.customer
GROUP BY first_name, last_name, expenses, room_rate
HAVING total_stay_cost < 50