i have a table order_match which contain order_buyer_id as the id of the transaction, createdby as the buyer, createdAt as the date when transaction begin, quantity as the amount of the transaction in kg (kilogram)
on this case i want to count average, minimum, maximum amount of quantity for each buyer who doing transaction more than one time at the range time, i've got my average, minimum, maximum, but i dont get the median value. this is the table example :
CREATE TABLE order_match
(`order_buyer_id` int, `createdby` int, `createdAt` datetime, `quantity` decimal(10,2))
;
INSERT INTO order_match
(`order_buyer_id`, `createdby`, `createdAt`, `quantity`)
VALUES
(19123, 19, '2017-02-02', 5),
(193241, 19, '2017-02-03', 5),
(123123, 20, '2017-02-03', 1),
(32242, 20, '2017-02-04', 4),
(32434, 20, '2017-02-04', 5),
(2132131, 12, '2017-02-02', 6)
;
and this is the syntax i've working on it
select
MAX(om.quantity) AS max,
MIN(om.quantity) AS min,
SUM(om.quantity)/ x1.count_ AS average
from (select count(xx.count_) as count_
from (select count(createdby) as count_ from order_match
group by createdby
having count(createdby) > 1) xx
) x1,
(select createdby
from order_match
group by createdby
having count(createdby) > 1) yy,
order_match om
where yy.createdby = om.createdby
and om.createdAt <= '2017-02-04'
and EXISTS (select 1 from order_match om2
where om.createdby = om2.createdby
and om2.createdAt >= '2017-02-02'
and om2.createdAt <= '2017-02-04')
pls help me to find out the median value for this case
this is the fiddle