Aggregate functions in sql in a activedataprovider

Hi,

I’m creating a dashboard like page with summarized data. I’d like to create a data provider to run this query:

select productName, sum(capacityRequested), sum(capacityApproved)

            from schedulingTransaction st 


            inner join product p on st.productID = p.productID


            where st.isVoid=0 and p.schedulingIO = 'I' and calendarDay='$someDate'


            group by productName

Is this possible to do in a CActiveDataProvider? I tried setting up a CDbCriteria to do it but I can’t seem to retrieve the data from the provider in the way that I would think. Here is my definition of my CDbCriteria:

$inputCriteria = new CDbCriteria;

        $inputCriteria->select = "p.productName, sum(t.capacityRequested) as capacityRequested, sum(t.capacityApproved) as capacityApproved";


        $inputCriteria->join = "inner join product p on t.productID = p.productID";


        $inputCriteria->condition = "t.isVoid=0 and p.schedulingIO = 'I' and t.calendarDay='$dateToShow'";


        $inputCriteria->group = "productName";


        $inputCriteria->order = "productName";

I then use a zii.widgets.CListView and try to access the data like so in my _view.php file:

<tr>


    <td><?php echo CHtml::encode($data[productName]); ?></td>


    <td><?php echo CHtml::encode($data->capacityRequested); ?></td>


    <td><?php echo CHtml::encode($data->capacityApproved); ?></td>


</tr>

What am I doing wrong? Can I even do what I want to do with Yii?

Thanks,

-Nazum

Yes. There are two ways to achieve it:

• statistical query

• CSqlDataProvider