Aggregate functions in sql in a activedataprovider


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:


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

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

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


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



Yes. There are two ways to achieve it:

  • statistical query

  • CSqlDataProvider