Where clause issue

Hello guys,

Working on: https://app.dataquest.io/m/190/building-and-organizing-complex-queries/4/creating-views

Could someone please tell me why this code is returning an error?

select
    iv.customer_id,
    sum(iv.total) as purchase
from invoice as iv
where purchase > 90.00
group by customer_id

I have managed to make it work in the following way, but I sense that there must be a cleaner way of doing this (or at least I would like to know the reason why it doesn’t work the way I heard it)

select * from (
select
    iv.customer_id,
    sum(iv.total) as purchase
from invoice as iv
group by customer_id)
where purchase > 90.00

Thank you all

1 Like

hi @Moshe

Where clause doesn’t allow aggregate functions hence the error. Purchase column is result of SUM function applied to total column.

For these cases we use having clause after group by, for example, try this query:

   SELECT 
       iv.customer_id,
       SUM(iv.total) AS purchase 
   FROM invoice as iv 
   GROUP BY customer_id
   HAVING purchase > 90.00
   LIMIT 4

for more info, please refer to this article: https://www.sqlitetutorial.net/sqlite-having/

1 Like

Right, thank u v much

1 Like