MySQL median with 2 group by

65 Views Asked by At

I have the following table

Date       | year        | quarter  |  val1   | val2        | 
-----------|-------------|----------|---------|-------------|
2020-01-01 | 2020        | Q1       | 10      | 80          | 
2020-01-01 | 2020        | Q1       | 20      | 20          |
2020-01-02 | 2020        | Q1       | 10      | 70          |
2020-01-03 | 2020        | Q1       | 30      | 40          |
2020-03-02 | 2020        | Q2       | 40      | 80          |
2020-03-02 | 2020        | Q2       | 20      | 20          |
...

what I try to get is a median group by year and quarter that includ a sum of val1 and val2 as well

year        | quarter  |sum(val1)   | sum(val2)   | median (val2)  |
------------|----------|------------|-------------|----------------|
2020        | Q1       | 70         | 210         | 55             |
2020        | Q2       | 60         | 100         | 50             |
...

I tried the following and it gave me the overall median for val2

SELECT AVG(dd.`val2`) as median_val2
FROM (
SELECT d.`val2`, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM `mytable` d, (SELECT @rownum:=0) r
  WHERE d.`val2` is NOT NULL
  ORDER BY d.`val2`
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

___

 median_val2    |
 ---------------|
 55             |

I understand that I have to add somewhere a group by but I don't know where.

0

There are 0 best solutions below