CODEWITHSUNDEEP
yii2yii2-basic-app
Sao Ho
Sao Ho

Reputation: 139

Yii2 combine multiple count and group by query

I have a project where they have 3 status. Now I need to count how many of each status each day and total count.

I can list them seperately. But don't know how to combine them.

Here's my code:

    $passed_counts = (new \yii\db\Query())
    ->select(['date', 'week', 'count(sku) as sku'])
    ->from('qa3d')
    ->where(['status' => 'Passed'])
    ->groupBy('date')->all();

    $failed_counts = (new \yii\db\Query())
    ->select(['date', 'week', 'count(sku) as sku'])
    ->from('qa3d')
    ->where(['status' => 'Failed'])
    ->groupBy('date')->all();

    $onhold_counts = (new \yii\db\Query())
    ->select(['date', 'week', 'count(sku) as sku'])
    ->from('qa3d')
    ->where(['status' => 'On Hold'])
    ->groupBy('date')->all();

    $total_counts = (new \yii\db\Query())
    ->select(['date', 'week', 'count(sku) as sku'])
    ->from('qa3d')
    ->groupBy('date')->all();

Right now with this I can list them seperately in 4 tables. I want to combine them in one table like this but don't know how.

date Passed Failed On Hold Total

2019-01-16 1 4 2 7

2019-01-17 1 0 2 3

Please help me with this.

Thank you.

Upvotes: 0

Views: 1807

Answers (2)

Poul
Poul

Reputation: 31

My solution was:

$images = ProductImages::find()
                ->where(['pi.product_id'=>$id])
                ->select([
                    'pi.*',   
                    'f.count as firstCount',
                    's.count as secondCount',
                ])
                ->from([
                    'pi' => ProductImages::tableName(),
                ])

                ->leftJoin(['f'=>First::find()
                    ->select('object_id, COUNT(*) as count')
                    ->where(['type'=>120])
                    ->groupBy(['object_id'])
                ], 'f.object_id = pi.id')

                ->leftJoin(['s'=>Second::find()
                    ->select('image_id, COUNT(*) as count')
                    ->where([
                        'type'=>101,
                        'is_personal'=>false,
                    ])
                    ->groupBy(['image_id'])
                ], 's.image_id = pi.id')

                ->asArray()
                ->all();

Upvotes: 0

Fabrizio Caldarelli
Fabrizio Caldarelli

Reputation: 3008

Try this:

$data = (new \yii\db\Query())
->select([
    'date', 
    'SUM( IF(status = "Passed", 1, 0) ) AS passed', 
    'SUM( IF(status = "Failed", 1, 0) ) AS failed', 
    'SUM( IF(status = "On Hold", 1, 0) ) AS onhold', 
    'COUNT(*) AS total'
])
->from('qa3d')
->groupBy('date')
->all();

Upvotes: 5

Related Questions