CODEWITHSUNDEEP
mysql
Bastien
Bastien

Reputation: 189

Select rows that fall within Standard Deviation

Here is my SQL:

SELECT    
    AVG(
      case 
         WHEN  `price` > (avg(`price`) - stddev_pop(`price`)) \
           AND `price` < (avg(`price`) + stddev_pop(`price`))
         THEN `price`
         ELSE NULL
      END)
FROM `history` 
WHERE `history`.`itemID` = 1574 \
      AND `date` > date_sub(now(), interval 30 DAY)

I am getting a #1111 - Invalid use of group function Error.

Basically, I want the average of price when price falls within standard deviation. If it does not, it should not be included in the Avg.

Example of price: 51, 48, 49, 56, 48, 56, 51, 58, 4, 56, 53

"4" should not be included while Avg is doing its thing.

Thanks!

Upvotes: 0

Views: 1237

Answers (1)

koriander
koriander

Reputation: 3258

see http://sqlfiddle.com/#!2/4905c/2

select avg(price)
from prices
join (select avg(price) myavg, stddev_pop(price) mydev from prices) stats
where price between stats.myavg - stats.mydev and stats.myavg + stats.mydev

Upvotes: 2

Related Questions