SQL request where clause

Question

I need to have results beyond 147 but when I execute my code workbench say me Error Code: 1054. Unknown column 'total_spend' in 'where clause'

SELECT
  customer_id,
  actor.first_name as first_name,
  actor.last_name as last_name,
  sum(amount) as total_spend
FROM payment
INNER JOIN actor
  ON customer_id = actor_id
where total_spend < 100
group by customer_id
order by total_spend desc;

Can you help me pls ?

To use a condition on an aggregate column, you need `HAVING` — Phil, Mar 02 '21 at 05:48

score 1 · Accepted Answer · answered Mar 02 '21 at 05:49

You need a having clause, also you cannot use the column alias in where/having clause

SELECT
  customer_id,actor.first_name as first_name,
  actor.last_name as last_name,sum(amount) as total_spend
FROM payment INNER JOIN actor ON customer_id = actor_id
group by customer_id
having sum(amount)<100
order by total_spend desc

SQL request where clause

1 Answers1