Getting maximum and minimum amount values ​​for mysql

Question

Good afternoon to everyone to whom I would like to receive a request that can give me both the maximum and minimum amounts. In particular, I have the following tables:

PRODUCT

_____________________________ productID | categoryID| name| __________|___________|_____| 1 1 "name1" 2 1 "name2" 3 1 "name3" 4 2 "name4" 5 2 "name5" 6 1 "name6" 7 2 "name7" 8 2 "name8" 

and

PURCHASES

 _____________________________ purchaseID | productID| quantity| ___________|___________|_________| 1 1 12 2 2 13 3 4 55 4 4 66 5 5 99 6 6 99 7 5 88 8 7 12 

so basically I have to show the product that was bought the most, and the product that was bought the least. T tried this:

 SELECT pr.name, max(sum(p2.quantity)) FROM purchase as p2, product as pr WHERE p2.IDproduct=pr.IDproduct Group by p2.IDproduct desc 

but I get error code 1111: incorrect use of a group function.

Answer 1

For maximum product

 select t.name,max(t.sum1) MaxProduct FROM (SELECT a.name, sum(b.quantity) sum1 FROM PRODUCT a INNER JOIN PURCHASES b ON a.productID = b.productID GROUP BY a.productID )t group by t.name order by MaxProduct desc limit 1 

FOR COMBINE RESULTS

 (select t.name,max(t.sum1) MaxProduct FROM (SELECT a.name, sum(b.quantity) sum1 FROM PRODUCT a INNER JOIN PURCHASES b ON a.productID = b.productID GROUP BY a.productID )t group by t.name order by MaxProduct desc limit 1) UNION ALL (select t1.name,min(t1.sum1) MaxProduct FROM (SELECT a.name, sum(b.quantity) sum1 FROM PRODUCT a INNER JOIN PURCHASES b ON a.productID = b.productID GROUP BY a.productID )t1 group by t1.name order by MaxProduct asc limit 1) 

SQL FIDDLE

Answer 2

Hacky but it works

 ( SELECT SUM(pur.quantity) quant, prod.name name FROM Purchases pur INNER JOIN Products prod ON prod.productID = pur.productID GROUP BY pur.productID ORDER BY quant DESC LIMIT 1 ) UNION ( SELECT SUM(pur.quantity) quant, prod.name name FROM Purchases pur INNER JOIN Products prod ON prod.productID = pur.productID GROUP BY pur.productID ORDER BY quant ASC LIMIT 1 ) 

SQLFiddle

Answer 3

 Select max(product_id), min(product_id) from Purchases Where Product_id In (select product_id, Sum(quantity) from Purchases Group by product_id)