mysql - php . How to find

Question

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 ?

Answer 1

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

Answer 2

Hi,

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!

Answer 3

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 :(