Return Multi Row Count


Looking for some assistance please.

I have a table that contains records of sports teams. within the table there are 2 fields that I need to use. one field is the compid which determines which comp a team is playing in. Then I have poolno, which identifies which pool within the competition a team is playing in. Sometimes due to the large numbers of players, the comp is divided into pools and players only play people within their pool.

What I want to return is a list of poolno’s and the count of the teams(records) in it for a specified competition.

condition: compid=X

group by: poolno

return: count(id)

comp  pool  count

1      1      12

1      2      12

1      3      14

so the output would be an array that would look something like:

array(1=>12, 2=>12, 3=>14)

Ive looked at the count method but it doesnt seem to be able to do what I need it to.

Currently I am getting the info one poolno at a time:

$html = $model->poolcount.' Pools: ';

for($i=1; $i<=$model->poolcount; $i++)


    $pcounts = XrefCompteam::model()->count('compid=:id AND poolno=:id2', array('id'=>$model->id, ':id2'=>$i));

    $html .= 'P'.$i.' = '.$pcounts.($i < $model->poolcount ? ', ' : '');


Just wondering if this can be simplified into a single statement that returns an array rather than having to run separate queries for each poolno.


Greg J