COUNT and AVG in 1 Query

[eluser]CI_Newb[/eluser]
Result I'm trying to get is as follows in a repeating table

Code:

agent_id - cnt - avg
agent_id - cnt - avg
agent_id - cnt - avg
etc...

Model

Code:

function get_agent_avgs()
    {
        $this->db->select('agent_id');
        $this->db->select('(select count(agent_id) as cnt
                from survey
                where submit_date like "'.YEAR_MONTH.'"
                and completed = "1") as cnt', TRUE);
        $this->db->select('(select avg(Q1) + avg(Q2_1) + avg(Q2_2) + avg(Q2_3) + avg(Q2_4) + avg(Q2_5) + avg(Q3) / 7 as avg
                from survey
                where submit_date like "'.YEAR_MONTH.'"
                and completed = "1") as avg', TRUE);
        $this->db->group_by('agent_id');
        $data = $this->db->get('survey');
        return $data;
    }

Controller

Code:

function agents()
    {
        $data['results'] = $this->survey_admin_model->get_agent_avgs();
        $this->load->view('survey/admin/agents_view', $data);
    }

View

Code:

<table class="sortable" width="450px" align="center" cellspacing="0">
<tr align="center" class="row_header">
<th align="left">Agent</th>
<th>Total</th>
<th>Average</th>
<th></th>
</tr>

&lt;?php foreach ($results->result() as $row):?&gt;
    <tr>
        <td align="left">&lt;?php echo $row->agent_id); ?&gt;</td>
        <td align="center">&lt;?php echo $row->cnt; ?&gt;</td>
        <td align="center">&lt;?php echo $row->avg; ?&gt;</td>
        <td></td>
    </tr>
    
&lt;?php endforeach; ?&gt;

</table>

but i'm sure you can tell this isn't working.

Suggestions?

[eluser]tonanbarbarian[/eluser]
have you created a query that you have tested against the database before you have tried to code it? if not you should
when working with complex queries you should always work out the query in advance and test it against real data using phpMyAdmin or similar to be sure the query performs as you expect

you should also try adding $this->db->last_query() into the mix so you can see exactly what query CI has just run

Code:

$data = $this->db->get('survey');
echo $this->db->last_query().'<br />';
return $data;

I believe the CI will be only running the last of the $this->db->select statements, but I could be mistaken as I never have more that 1 $this->db->select statement in my code

I think your query may be needlessly complex. You have sub queries where you problably do not need them, and you are essentially averaging averages, which I am not sure you want to do.

I think you are trying to do the following

Code:

SELECT agent_id, COUNT(agent_id) AS cnt, SUM(Q1 + Q2_1 + Q2_2 + Q2_3 + Q2_4 + Q2_5 + Q3) / 7 AS avg
FROM survey
WHERE submit_date LIKE '[YEAR_MONTH]'
AND completed = 1
GROUP BY agent_id

however if you have an original tested query that you are working off then please provide it

The SQL AVG function will find the average of the values in a column. So in the case you had you were finding the average for the values in each of 7 columns, adding them together and dividing by 7, thus giving you the average of an average.

Just not sure if this is intended or not

[eluser]CI_Newb[/eluser]
Good point, i'll start running these queries in phpmyadmin.

As for the averages, basically what I want to do is average each column, and then get the total average from each column by adding and then dividing by 7 to get a overall average.