Limit with COUNT AS

[eluser]CI_Newb[/eluser]
What I want to do is get a "top 10 agents" list. This would be easy but what I want to do is have it for agents with a TOTAL over 10.

I have the count as total in there, but can't figure out how to exclude anyone with a TOTAL under 10.

Model

Code:

function get_top_agents()
    {
        $this->db->select('agent_id');
        $this->db->select('COUNT(agent_id) AS TOTAL');
        $this->db->select('(AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG');
        $this->db->where('completed', '1');
        $this->db->like('submit_date', YEAR_MONTH);
        $this->db->group_by("agent_id");
        $this->db->order_by("AGENT_AVG", "desc");
        //$this->db->limit(10);
        $data = $this->db->get('survey');
        return $data;
    }

View

Code:

<?php foreach (@$top_agents->result() as $row):
            @$count++;
            if(($count % 2) == 1){
            $class = "row0";
            } else {
            $class = "row1";
            }
            ?>
    <tr>
        <td align="left"><b>&lt;?php echo $row->ns_agent_id; ?&gt;</b></td>
        <td align="center">&lt;?php echo $row->TOTAL; ?&gt;</td>
        <td align="center">&lt;?php echo round($row->AGENT_AVG, 2); ?&gt;</td>
        <td></td>
    </tr>
&lt;?php endforeach; ?&gt;

Any help would be greatly appreciated


EDIT: Friend said I should clarify what i'm trying to accomplish a bit more.

This is invalid code, but basically what I want to accomplish

Code:

function get_top_agents()
    {
        $this->db->select('agent_id');
        $this->db->select('COUNT(agent_id) AS TOTAL');
        $this->db->select('(AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG');
        $this->db->where('completed', '1');
        
        $this->db->where('TOTAL >=', '10');
        
        $this->db->like('submit_date', YEAR_MONTH);
        $this->db->group_by("agent_id");
        $this->db->order_by("AGENT_AVG", "desc");
$this->db->limit(10);
        $data = $this->db->get('survey');
        return $data;
    }

[eluser]tonanbarbarian[/eluser]

Code:

function get_top_agents()
    {
        $this->db->select('agent_id');
        $this->db->select('COUNT(agent_id) AS TOTAL');
        $this->db->select('(AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG');
        $this->db->where('completed', '1');
        
        $this->db->where('COUNT(agent_id) >=', '10');
        
        $this->db->like('submit_date', YEAR_MONTH);
        $this->db->group_by("agent_id");
        $this->db->order_by("AGENT_AVG", "desc");
$this->db->limit(10);
        $data = $this->db->get('survey');
        return $data;
    }

[eluser]CI_Newb[/eluser]
Thanks for the reply!

Getting
"A Database Error Occurred

Error Number: 1111

Invalid use of group function"

[eluser]tonanbarbarian[/eluser]
what you want to do is possible but not using the active record coding in CI

the following query should work

Code:

SELECT * FROM (
SELECT agent_id, COUNT(agent_id) AS TOTAL, (AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG
FROM survey
WHERE completed= 1        
AND submit_date like YEAR_MONTH
GROUP BY agent_id
ORDER BY AGENT_AVG desc) AS new_survey
WHERE new_survey.total >= 10
LIMIT 0, 10

so you should just run it using the query

Code:

$query = $this->db->query('SELECT * FROM (
SELECT agent_id, COUNT(agent_id) AS TOTAL, (AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG
FROM survey
WHERE completed= 1        
AND submit_date like YEAR_MONTH
GROUP BY agent_id
ORDER BY AGENT_AVG desc) AS new_survey
WHERE new_survey.total >= 10
LIMIT 0, 10');

[eluser]CI_Newb[/eluser]
[quote author="tonanbarbarian" date="1290706331"]what you want to do is possible but not using the active record coding in CI

the following query should work

Code:

SELECT * FROM (
SELECT agent_id, COUNT(agent_id) AS TOTAL, (AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG
FROM survey
WHERE completed= 1        
AND submit_date like YEAR_MONTH
GROUP BY agent_id
ORDER BY AGENT_AVG desc) AS new_survey
WHERE new_survey.total >= 10
LIMIT 0, 10

so you should just run it using the query

Code:

$query = $this->db->query('SELECT * FROM (
SELECT agent_id, COUNT(agent_id) AS TOTAL, (AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 AS AGENT_AVG
FROM survey
WHERE completed= 1        
AND submit_date like YEAR_MONTH
GROUP BY agent_id
ORDER BY AGENT_AVG desc) AS new_survey
WHERE new_survey.total >= 10
LIMIT 0, 10');

[/quote]

Thanks for the code!

So I tried it at work but forgot to reply. Query works, but I'm still coming up with totals under 10.

BUT i just looked at your code again. Am I supposed to echo TOTAL or new_survey.total?

[eluser]vitoco[/eluser]
when you use GROUP BY, and wanna to cut the results based in COUNT() , AVG() or any row resulting from a operation of rows, use HAVING

Code:

$sql = "
    SELECT
        agent_id ,
        COUNT(agent_id) AS TOTAL ,
        ( (AVG(Q1)+ AVG(Q2_1)+ AVG(Q2_2)+ AVG(Q2_3)+ AVG(Q2_4)+ AVG(Q2_5)+ AVG(Q3)) / 7 ) AS AGENT_AVG
    FROM
        survey
    WHERE
        completed    = 1 AND
        submit_date    LIKE '".YEAR_MONTH."'
    GROUP BY
        agent_id
    HAVING
        TOTAL >= 10
    ORDER BY
        AGENT_AVG DESC
    LIMIT
        0 , 10
    ";

    $query = $this->db->query( $sql );

[eluser]CI_Newb[/eluser]
Booya, that did the trick vitoco!

Never thought of using HAVING.

[eluser]vitoco[/eluser]
glad it helps.

Saludos