Hi,
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.
Regards
Greg J