CODEWITHSUNDEEP
mysqlphpmyadmin
qwe qwe
qwe qwe

Reputation: 3

orders that have more than 1 product

This is my database

I need to show

1) Orders that have more than 1 product

2) List of sold quantities for each product

ORDERS

Number_ord(PK)  Buyer   Delivery_address    Date

1               Martin  Chicago             2018-12-16
2               John    Berlin              2018-12-17
3               Martin  Chicago             2018-12-18


PRODUCTS

Number_prod Number_ord(FK)  Qty
469841      1               15
469841      2               15
999999      2               6
558585      2               45
469841      3               15
844444      3               80

1)I wrote something like that, but it's wrong

SELECT orders.Number_ord 
FROM orders 
join products USING(Number_ord) 
GROUP BY COUNT(Number_prod)>1

2)Here, I have a problem too 

SELECT count(products.Number_prod),sum(products.Qty) 
FROM orders 
join products USING(Number_ord) 
GROUP BY Number_prod

Please help me

Upvotes: 0

Views: 37

Answers (1)

Barmar
Barmar

Reputation: 781078

You need to use HAVING to filter based on the count.

SELECT orders.Number_ord, SUM(products.Qty)
FROM orders
JOIN products USING (Number_ord)
GROUP BY orders.Number_ord
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions