0

I want to create a function that I can use to calculate median in MySQL. I found this article but I can't wrap my head around it. Is there any easy way to create a function to calculate the median in MySQL. Below is my code. I want to use median instead of avg.

Updated Query

select t1.brain_sku
    ,avg(weeklysales_4wks) as Avg_4Weeks /* Replace avg with median */
    ,avg(weeklysales_12wks) as Avg_12Weeks /* Replace avg with median */
from (
        select brain_sku, week(order_date) as week_date, sum(quantity_ordered) as weeklysales_4wks
        from sales
        WHERE date(order_date) > date(DATE_SUB(NOW(), INTERVAL 4 WEEK))
        and IF(DAYNAME(NOW()) != 'Sunday',  week(order_date) != week(now()), week(order_date) <= week(now()) )
        AND brain_sku in ('1400280','1177260')
        GROUP BY brain_sku, week(order_date)
        ) t1
RIGHT JOIN (
        /*select t2.brain_sku, avg(t2.weeklysales_12wks) as weeklysales_12wks from (*/
        select brain_sku, week(order_date) as week_date, sum(quantity_ordered) as weeklysales_12wks
        from sales
        WHERE date(order_date) > date(DATE_SUB(NOW(), INTERVAL 12 WEEK))
        and IF(DAYNAME(NOW()) != 'Sunday',  week(order_date) != week(now()), week(order_date) <= week(now()) )
        and brain_sku in ('1400280','1177260')
        GROUP BY brain_sku, week(order_date)
            ) t2
        /*GROUP BY t2.brain_sku
            ) t3 */
ON t1.brain_sku = t2.brain_sku
GROUP BY t1.brain_sku

Any help will be appreciated.

hkay
  • 159
  • 1
  • 2
  • 12
  • What version of mysql? PS search for stack overflow mysql median. – P.Salmon Oct 26 '21 at 15:31
  • @P.Salmon Thanks for the comments. I am using MySQL 5.6. I tried looking but could not find a way to create a function for median. I am a T-SQL person so I'm a little struggling with MySQL. – hkay Oct 26 '21 at 16:28

0 Answers0