Get the median from 6 tables

Question

I know how to calculate the median from 1 table but Im trying to do it on 6 tables comparing the price. My code so far, can you help?

    select avg(price) as median from
     (select row_id, price from (
     (select @counter:=@counter+1 as row_id, t1.priceInt as price
     from Table1 t1, (select @counter:=0) tx1
     )
     union all (select @counter:=@counter+1 as row_id, t2.priceInt as price
     from Table2 t2, (select @counter:=0) tx2
     )
     union all (select @counter:=@counter+1 as row_id, t3.priceInt as price
     from Table3 t3, (select @counter:=0) tx3
     )
     union all (select @counter:=@counter+1 as row_id, t4.priceInt as price
     from Table4 t4, (select @counter:=0) tx4
     )
     union all (select @counter:=@counter+1 as row_id, t5.priceInt as price
     from Table5 t5, (select @counter:=0) tx5
     )
     union all (select @counter:=@counter+1 as row_id, t6.priceInt as price
     from Table6 t6, (select @counter:=0) tx6
     )
     ) xx order by price) o1 join
     (
     select sum(x) AS total_rows from
     (
     select count(*) x from Table1 union all select count(*) x from 
     Table2
     union all select count(*) x from Table3 union all select count(*) x 
     from Table4
     union all select count(*) x from Table5 union all (select count(*) x 
     from Table6
     ) 
     ) o2 where o1.row_id in (floor((o2.total_rows + 1)/2), 
    floor((o2.total_rows + 2)/2)))

My error is that o1.row_id is not recognised!

Here is an example of Table 1, each table has identical columns!

***EDIT

Desired results: 250, 275, 300, 400, 500 I want the 300k (Note the numbers have to be ordered and if there are 2 middle numbers the average has to be found of the 2 numbers)

Answer 1

Just "stack" the data from each of the tables together, and treat that as one list from then on, Do the counter "up one level". Layout your query so it is easy to read and so that aliases are easy to find.

select
     avg(price) as median 
from (
        select
               row_id
             , price 
        from (     
            select
                   @counter:=@counter+1 as row_id
                 , price     
            from (
                 select t1.priceInt as price from Table1 t1 union all 
                 select t2.priceInt as price from Table2 t2 union all 
                 select t3.priceInt as price from Table3 t3 union all 
                 select t4.priceInt as price from Table4 t4 union all 
                 select t5.priceInt as price from Table5 t5 union all 
                 select t6.priceInt as price from Table6 t6 
                 ) u
            cross join (select @counter:=0) vars
            ORDER BY u.price
            ) o1
        cross join (
                     select sum(x) AS total_rows 
                     from (
                         select count(*) x from Table1 union all 
                         select count(*) x from Table2 union all 
                         select count(*) x from Table3 union all 
                         select count(*) x from Table4 union all 
                         select count(*) x from Table5 union all 
                         select count(*) x from Table6
                        ) c
                  ) o2
        where o1.row_id in (floor((o2.total_rows + 1)/2),floor((o2.total_rows + 2)/2)))
    ) d

Placing repetitive sql as a "list" is very helpful in my experience. I just realized I had the table names wrong just before i saved this. It's untested of course but it should help you get going.

Oh, and please avoid using commas between tables or subqueries in the from clause. You will see lots of samples like this:

from table_x, (select @counter:=0) vars

Don't! It isn't an explicit join (it is an implied cross join) make it explicit:

from table_x
cross join (select @counter:=0) vars

Now everyone know that cross join exsts and is deliberate.