CODEWITHSUNDEEP
mysql
Chameron
Chameron

Reputation: 2974

mysql - php . How to find

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 ?

Upvotes: 0

Views: 67

Answers (3)

Chameron
Chameron

Reputation: 2974

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

Upvotes: 0

Alin P.
Alin P.

Reputation: 44346

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!

Upvotes: 1

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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

Upvotes: 0

Related Questions