'how do I use JOIN, WHERE AND GROUP BY in the same query

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.

sqlpostgresql


Solution 1:[1]

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);

Solution 2:[2]

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.

Solution 3:[3]

Not a beauty, however you may try some kind of subselect. Additionally, I think, you missed out on the Yearly_Budget in the "Group By"

Select * from (
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, yearly_budget, year_purchased) a
where a.yearly_budget < a.total_price;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jörg Albrecht
Solution 2 Soccan
Solution 3 Jörg Albrecht

Related Questions