How to get median in mysql if group by clause?

user_13883723 0
Viewed 147

My mysql version is 8.+.

Table structure:

CREATE TABLE `loss` (
  `date` date DEFAULT NULL,
  `circle` varchar(100) DEFAULT NULL,
  `district` varchar(100) DEFAULT NULL,
  `kpi_1` int(11) DEFAULT NULL,
  `kpi_2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data for the table loss:

insert  into `loss`(`date`,`circle`,`district`,`kpi_1`,`kpi_2`) values 
('2020-09-20','101','delhi',90,100),
('2020-09-20','102','Punjab',80,10),
('2020-09-20','104','delhi',90,90),
('2020-09-20','104','New Delhi',20,10),
('2020-09-20','104','Punjab',45,23),
('2020-09-20','104','New Delhi',4,13),
('2020-09-20','104','New Delhi',7,150),
('2020-09-20','104','New Delhi',80,40),
('2020-09-20','104','New Delhi',80,50),
('2020-09-20','104','New Delhi',NULL,NULL);

Query:

select date,circle,district,count(*) as total_rows,sum(kpi_1),sum(kpi_2) from loss
group by date,circle,distrcit

I can get all aggregates, but how to find median as well?

median window-functions count mysql sql
1 Answers

Unfortunately, MySQL does not have an aggregate median function or the-like - not even as a window function, as in MariaDB.

One workaround using window functions is:

select date, circle, district, count(*) cnt, sum(kpi_1) sum_kpi1, sum(kpi_2) sum_kpi2,
    avg(case when rn1 in (floor((cnt + 1)/2), floor((cnt + 2)/2)) then kpi1 end) media_kpi1,
    avg(case when rn2 in (floor((cnt + 1)/2), floor((cnt + 2)/2)) then kpi2 end) media_kpi2
from (
    select l.*, 
        row_number() over(partition by date, circle, district order by kpi1) rn1,
        row_number() over(partition by date, circle, district order by kpi2) rn2,
        count(*)     over(partition by date, circle, district) cnt
    from loss
) l
group by date, circle, district

This assumes that you want to aggregate records by date, circle and district, as show in your original query. If you want another set of columns, then you can change both the group by clause and the partitions of the window functions.

user_106767160
Related