0

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

http://sqlfiddle.com/#!9/712b43/1

Strawberry
  • 32,714
  • 12
  • 37
  • 56
18Man
  • 540
  • 2
  • 13
  • Does this answer your question? [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) – pritaeas Feb 26 '20 at 10:38
  • can you explain it sir and maybe make it help with your new syntax because i've tried your suggestion but it still cant happen. – 18Man Feb 26 '20 at 10:40

0 Answers0