i have 2 table
users : id , name
order : id , price, user_id
a users have more than one order. And i want to select a users
who have total price >100 . how to do ?
EX : user John have 3 orders with price : 30$, 40$ , 50$
SELECT * FROM user u
INNER JOIN order o ON o.user_id = u.id
WHERE sum(o.price) >100
this query will get the user is john ? 30+40+50 = 110 >100 ?
First of all you are not allowed to use aggregate functions (i.e. SUM) in your WHERE clause. They should be used in the HAVING clause. To compute aggregates you need to GROUP BY something, in your case by u.id.
So:
SELECT *
FROM user u
INNER JOIN order o ON u.user_id = u.id
GROUP BY u.id
HAVING sum(o.price) > 100
Note that if you would need users with no order you would have to use LEFT JOIN!
SELECT u.Id, Sum(o.price) FROM user u
INNER JOIN order o ON o.user_id = u.id
GROUP BY u.Id
HAVING sum(o.price) > 100
Thank for all answer but i catch problem now i have a new table order_file
users : id , name
order : id , price, user_id
order_file : id , file , order_id
users, order is one-many
order , order_file is one-many
a user John have 1 order with price is 200$ and this order link to order_file with two record
SELECT *
FROM user u
INNER JOIN order o ON u.user_id = u.id
INNER JOIN order_file of ON of.order_id = o.id
WHERE u.name = 'John'
we will get two rows.Only diff value in order_file
now to get once i use DISTINCT , and i get 1 row
SELECT DISTINCT *
FROM user u
INNER JOIN order o ON u.user_id = u.id
INNER JOIN order_file of ON of.order_id = o.id
WHERE u.name = 'John'
then i do SUM , oh la la , i get 400$ , not 200$
SELECT DISTINCT *, sum(o.price)
FROM user u
INNER JOIN order o ON u.user_id = u.id
INNER JOIN order_file of ON of.order_id = o.id
WHERE u.name = 'John'
how to i can get extracty 200$ , not duplicate row :(
