Geek Answers Handbook

Using min, max and avg in mysql query

I have a table like below.

I want product_id from products with minimum, maximum and average cost in one request.

CREATE TABLE productlist(product_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, cost INT); INSERT INTO productlist(cost) VALUES('2450'), ('2200'), ('2580'), ('2405'), ('3500'), ('1500'), ('1800'), ('1520'), ('1740'), ('1940'), ('2940'), ('1250'), ('1290'), ('1390'), ('2900');

Output:

 Min 12 Max 5 Avg 2093

I tried as one below, but did not work.

 SELECT product_id, MIN(cost) as mincost FROM productlist GROUP BY product_id ORDER BY mincost ASC LIMIT 0,1 UNION SELECT product_id, max(cost) as maxcost FROM productlist GROUP BY product_id ORDER BY maxcost DESC LIMIT 0,1

How can I do it

+4
source share
5 answers
 select 'Min', product_id from productlist where cost = (select min(cost) from productlist) UNION select 'Max', product_id from productlist where cost = (select MAX(cost) from productlist) UNION select 'Avg', round(AVG(cost),0) as Avg from productlist
+1
source 
 select product_id, cost from productlist where cost = (SELECT max(cost)from productlist) union select product_id, cost from productlist where cost = (SELECT min(cost)from productlist) union select product_id, cost from productlist where cost = (SELECT x.cost from productlist x, productlist y GROUP BY x.cost HAVING SUM(SIGN(1-SIGN(y.cost-x.cost))) = (COUNT(*)+1)/2)

It uses the median, returns the product identifier in each case

+1
source

This answers your question exactly, but it should be noted that 3 scan tables are required to find this data. In addition, the example in the question suggests that the average value is truncated to 2093 from 2093.67. It might be better to replace this with round .

SQL Fiddle

 SELECT concat('Min ', product_id) FROM productlist WHERE cost = (SELECT min(cost) from productlist) UNION ALL SELECT concat('Max ', product_id) FROM productlist WHERE cost = (SELECT max(cost) from productlist) UNION ALL SELECT concat('Avg ', truncate(avg(cost), 0)) FROM productlist
0
source

the conclusion that you want does not come at the request that you wrote you need to try this to get the required output

 select 'Min', product_id from productlist where cost = (select min(cost) from productlist) UNION select 'Max', product_id from productlist where cost = (select MAX(cost) from productlist) UNION select 'Avg', floor(AVG(cost)) as Avg from productlist
0
source

This is for selecting all products.

  SELECT max(cost), MIN(cost), AVG(cost) FROM productlist GROUP BY product_id

GROUP BY is not required here. But it looks like you are new, googling will help you.

For your question try this.

 SELECT (select CONCAT(product_id, '-', cost) from productlist group by product_id order by cost DESC limit 1) as MAX, (select CONCAT(product_id, '-', cost) from productlist group by product_id order by cost ASC limit 1) as MIN, (select avg(cost) from productlist) as AVG FROM productlist limit 1
-1
source

More articles:


All Articles