Calculating the median value in a MySQL table with CodeIgniter This is my model file in codeigniter. I need to calculate the median for this variable: budget_breakdown.breakdown_grants. How can I do this calculation?

function reports_details1() {
        $this-&gt;db-&gt;select('budget_breakdown.breakdown_grants');
    //survey_respondent_info.state,survey_respondent_info.survey_id,budgets.budget_id,
        $this-&gt;db-&gt;from('survey_respondent_info');
        $this-&gt;db-&gt;join('budgets',
                        'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
        $this-&gt;db-&gt;join('budget_breakdown',
                            'budgets.budget_id=budget_breakdown.budget_id' , 'left');
        $this-&gt;db-&gt;where('budgets.budget_option_id', 2);
        $query1 = $this-&gt;db-&gt;get();
        $result = $query1-&gt;result();
        return $result;
    }
Not being familiar with codeigniter, I am doing this first in SQL, and then make a guess on how to translate it back into codeigniter:

I translated your query to SQL first"

SELECT budget_breakdown.breakdown_grants
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;


Now, I alter the query to find the midpoint:

SELECT CEIL(COUNT(*)/2 
INTO @midpoint
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;


Finally, I use the @midpoint variable to get the median:

PREPARE STMT FROM
   'SELECT max(budget_breakdown.breakdown_grants) FROM (
    SELECT budget_breakdown.breakdown_grants
    FROM survey_respondent_info
    LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
    LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
    WHERE budgets.budget_option_id  = 2
    ORDER BY budget_breakdown.breakdown_grants LIMIT ?) x';

EXECUTE STMT USING @midpoint;


I went through the PREPARE and EXECUTE, because LIMIT doesn't work directly with a variable, this is a workaround. 

Now put it back into codeigniter:

function reports_median() {

    $this-&gt;db-&gt;select('CEIL(COUNT(*)/2');
    $this-&gt;db-&gt;from('survey_respondent_info');
    $this-&gt;db-&gt;join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this-&gt;db-&gt;join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this-&gt;db-&gt;where('budgets.budget_option_id', 2);
    $query1 = $this-&gt;db-&gt;get();
    $midpoint = $query1-&gt;result();
    $midpoint = $midpoint/2;

    $this-&gt;db-&gt;select('max(budget_breakdown.breakdown_grants');
    $this-&gt;db-&gt;from('survey_respondent_info');
    $this-&gt;db-&gt;join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this-&gt;db-&gt;join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this-&gt;db-&gt;where('budgets.budget_option_id', 2);
    $this-&gt;db-&gt;order_by('budget_breakdown.breakdown_grants', 'asc');
    $this-&gt;db-&gt;limit($midpoint);
    $query2 = $this-&gt;db-&gt;get();
    $median = $query2-&gt;result(); 

} 


As I said, I am not familiar with codeigniter and can't test that part. It is a good guess.