How to calculate the median time when grouped by multiple columns?
| order_start | order_ready | COMPANY | id |
|---|---|---|---|
| 2018-01-01 18:55:00 | 2018-01-01 19:05:00 | x | 223 |
| 2018-01-01 18:55:00 | 2018-01-01 19:05:00 | x | 224 |
| 2018-01-01 18:55:00 | 2018-01-01 19:05:00 | y | 225 |
| 2018-01-01 18:55:00 | 2018-01-01 19:05:00 | y | 226 |
What I can do using
SELECT
COUNT(id) as count,
COMPANY as company ,
YEAR(order_start) as year,
MONTH(order_start) as month
FROM table
GROUP BY
YEAR(order_start),
MONTH(order_start),
COMPANY;
| count | company | year | month |
|---|---|---|---|
| 2 | x | 2018 | 1 |
| 2 | y | 2018 | 1 |
What I want is also the median order time calculated on the orders within each company, year, and month
| count | company | year | month | median_order_time |
|---|---|---|---|---|
| 2 | x | 2018 | 1 | 10 |
| 2 | y | 2018 | 1 | 10 |
I can calculate the waiting time for each trip using the below query, but how do I calculate the median to produce the last table? I am using SQL Server 2017
DATEDIFF(MINUTE, order_start, order_ready))
