I've started using SQL (postgresql to be specific) and I'm wondering why my query that uses a JOIN WHERE and GROUP BY doesn't work

I have two tables and its the classic one-to-many relationship. for example I have one table called users the other is products. A user has many products.

# users columns
id
user_name
yearly_budget

# products columns
id
user_id
price
product_name
purchased_at(datetime)

a user has yearly budget that. the query I want to get all the users (or just one) and group the day by year and sum up all the prices of products the user has spent and then I want to compare that against their yearly budget to see if they went over it for that year.

this is the SQL query I have so far.

SELECT
    users.id,
    yearly_budget,
    sum(price) as total_price,
    date_part('year', purchased_at)  as year_purchased
FROM users 
JOIN products on products.user_id = users.id 
WHERE yearly_budget < total_price
GROUP BY users.id, year_purchased;

but I keep getting the error column "total_price" does not exist I really don't understand what that means. If I remove the last WHERE statement I can clearly see the total_price column.

CodePudding user response：

I think you are getting tripped by the order of which those statements are actually executed, the total_price is an alias that in fact does not exist when the where part of the query is executed, you need to write sum(price)instead.

CodePudding user response：

I come from the Oracle Side of SQL. There you have a HAVING clause which works just like a WHERE clause only that it enables you to access results of a group by. It should look something like:

SELECT
    users.id,
    yearly_budget,
    sum(price) as total_price,
    date_part('year', purchased_at)  as year_purchased
FROM users 
JOIN products on products.user_id = users.id 
GROUP BY users.id, year_purchased
HAVING yearly_budget < sum(price);