• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Complex Database query--Help Please

#1
I am tgrying to find the median value in a table.

This works:

Code:
SELECT round(AVG(middle_values),0) AS 'median' FROM (
 SELECT t1.masterpoints_total AS 'middle_values' FROM
   (
     SELECT @row:[email protected]+1 AS `row`, x.masterpoints_total
     FROM members AS x, (SELECT @row:=0) AS r
     WHERE 1
     -- put some where clause here
     ORDER BY x.masterpoints_total
   ) AS t1,
   (
     SELECT COUNT(*) AS 'count'
     FROM members x
     WHERE 1
     -- put same where clause here
   ) AS t2
   -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
   WHERE t1.row >= t2.count/2 AND t1.row <= ((t2.count/2) +1)) AS t3;

I am not sure how to write the query buidler.

Thanks.

Bill Hogsett
Reply

#2
You don't need to use the query builder for everything, just run your query:
https://www.codeigniter.com/user_guide/d...eries.html

It will also be faster, as it dosen't need to be run through some PHP functions on the way.
Reply

#3
(11-19-2018, 02:32 PM)jreklund Wrote: You don't need to use the query builder for everything, just run your query:
https://www.codeigniter.com/user_guide/d...eries.html

It will also be faster, as it dosen't need to be run through some PHP functions on the way.

Thanks, this is what I needed and had missed.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.