Count Low, Median, Average, High using MySql query

Question

I have following records in my table:

  Name            Status          Price

Product 1         Active           110
Product 2         Active           165
Product 3         Expire           256
Product 4         Pending          154
Product 5         Active           856
Product 6         Expire           523
Product 7         Pending          220
Product 8         Active           321
Product 9         Pending          478
Product 10        Expire           210

and I need output by mysql query as follow:

 Status       Low         Median          Average         High

 Active        ?            ?               ?              ?
 Expire        ?            ?               ?              ?
 Pending       ?            ?               ?              ?

I don't know how to do this by mysql query.

Thanks in advance.

yes I want to calculate Low, Median, Average and High value of price — yogesh suhagiya, Sep 03 '14 at 05:39
I have trid this query: "SELECT ROUND(AVG(Price),2) as Avg, MAX(Price) AS High, MIN(Price) AS Low FROM Table WHERE 1 GROUP BY Status ORDER BY Status". but this query calculate only Low, High and Avg not Median value. — yogesh suhagiya, Sep 03 '14 at 05:55
I want to calculate Low, Median, Average and High value of price by status wise. Question marks are replaced with calculated price value, so for status "Active" : Low value is: 110, High value is: 856, etc .... — yogesh suhagiya, Sep 03 '14 at 08:00

Satish Sharma · Answer 1 · 2014-09-03T06:15:03.880

3

SELECT status, MIN(price) as Low, MAX(price) as High, AVG(price) as Average 
FROM  your_table
GROUP BY status

edited Sep 03 '14 at 06:15

answered Sep 03 '14 at 05:40

Satish Sharma

9,217
6
24
49

But how can I calculate Median value with the same query ? – yogesh suhagiya Sep 03 '14 at 05:49
for median value you have to run another sql if you will want to calcuate median in this query will be too messay. see http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql – Satish Sharma Sep 03 '14 at 05:51
my table and code is too heavy to run another query, there are thousands of records that's why I need it in single query – yogesh suhagiya Sep 03 '14 at 05:58

score 1 · Accepted Answer · answered Sep 12 '14 at 12:50

try following code:

<?php

    $arr = array( 'Active', 'Expire', 'Pending');

    foreach($arr as $status) {

        $stmt = "SELECT a.max, a.min, a.avg, price AS med, IF(price > a.avg, price - a.avg, a.avg-price ) AS diff FROM tbl_report_address_lists, ( SELECT ROUND(AVG(price),2) as avg, MAX(price) AS max, MIN(price) AS min FROM tbl_report_address_lists WHERE `report_id` = 13 AND `status` = '$status') AS a WHERE `report_id` = 13 AND `sales_code` = '$status' ORDER BY diff ASC LIMIT 1";

        // execute query, get result data and use it    
    }

?>

score 0 · Answer 3 · answered Sep 03 '14 at 09:36

I have prepared following query to calculate High, Low, Med and Avg, but this query calculate prices only for single status at a time:

SELECT STATUS , a.max AS High, a.min AS Low, a.avg AS Avg, Price AS Med, IF( Price > a.avg, Price - a.avg, a.avg - Price ) AS diff
FROM Table, (
  SELECT ROUND( AVG( Price ) , 2 ) AS avg, MAX( Price ) AS max, MIN( Price ) AS min
  FROM Table
  WHERE STATUS =  'Pending'
) AS a
WHERE STATUS =  'Pending'
ORDER BY diff ASC 
LIMIT 1

If I removed WHERE STATUS = 'Pending' condition, return inaccurate result.

Count Low, Median, Average, High using MySql query

3 Answers3