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

Answer 2

Your query has multiple issues:

• don't use single quotes for identifiers! They stand for literal strings.

• you can't reuse an alias defined in the select clause in the where clause (the latter is evaluated before the former).

• the aggregation part is off: either missing a group by clause, or you should not be using aggrgate functions in the select clause

If you want one result per row that satisfies the condition, then:

select c.*,
    stays_num * room_rate as total_room_cost,
    stays_num * room_rate + expenses as total_stay_cost
from assign1.customer c
where stays_num * room_rate + expenses > 50

If you want, say, one row per customer, then:

select c.customer_id, c.first_name, c.last_name,
    sum(stays_num * room_rate) as total_room_cost,
    sum(stays_num * room_rate + expenses) as total_stay_cost
from assign1.customer c
group by c.customer_id
having total_stay_cost > 50