Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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->db->select('budget_breakdown.breakdown_grants');
    //survey_respondent_info.state,survey_respondent_info.survey_id,budgets.budget_id,
        $this->db->from('survey_respondent_info');
        $this->db->join('budgets',
                        'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
        $this->db->join('budget_breakdown',
                            'budgets.budget_id=budget_breakdown.budget_id' , 'left');
        $this->db->where('budgets.budget_option_id', 2);
        $query1 = $this->db->get();
        $result = $query1->result();
        return $result;
    }
share|improve this question
    
Search for 'median' in the docs. –  dezso Aug 2 '13 at 11:52
add comment

1 Answer

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->db->select('CEIL(COUNT(*)/2');
    $this->db->from('survey_respondent_info');
    $this->db->join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this->db->join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this->db->where('budgets.budget_option_id', 2);
    $query1 = $this->db->get();
    $midpoint = $query1->result();
    $midpoint = $midpoint/2;

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

} 

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.