Yii2 querybuilder sum with condition

Asked Jan 04 '18 at 04:21

Active Jan 04 '18 at 08:09

Viewed 251 times

This is my query.

$query1 = (new \yii\db\Query())
    ->select(['day', 'modeler', 'sum(ifnull(differ, 0)) + sum(ifnull(reworkdiffer, 0)) as total'])
    ->from('loghour3d')
    ->groupBy(['day', 'modeler'])
    ->orderBy(['day'=>SORT_DESC]);

This is my table structure.

day          modeler    differ    handover     reworkdiffer 
2018-1-05     star        5                         
2018-1-06     star                 star1            3 
2018-1-06     star1       3

So i want to sum differ field grouping by day and modeler. My code get me this result:

day          modeler    total  
2018-1-05     star        5                         
2018-1-06     star        8        
2018-1-06     star1       3

Above, In the date 2018-1-06, modeler star didn't work, instead handover to star1, but total is sum to star instead of star1. Below is the result i want

    day          modeler    total  
    2018-1-05     star        5                         
    2018-1-06     star        0        
    2018-1-06     star1       6

Is there anyway for me to archive this with querybuilder in yii2? Please advice

Thank you.

edited Jan 04 '18 at 07:38

asked Jan 04 '18 at 04:21

Sao Ho

[Reference](https://stackoverflow.com/questions/2179207/sum-values-of-a-single-row) – Insane Skull Jan 04 '18 at 04:49
I can help you but how do you get 6 for star1 ? – kiamoz Jan 04 '18 at 06:18
@kiamoz in the date "2018-1-06" `star` handover to `star1` and `star1` works 3 hours, in the same day `star1` works 3 more hours, so total in the date "2018-1-06" of `star` is 0 and `star1` is 6. – Sao Ho Jan 04 '18 at 06:52
I undrestod let me know you have 2 tables ? – kiamoz Jan 04 '18 at 07:01
no, i have one, I've mentioned my table structure above :) – Sao Ho Jan 04 '18 at 07:02

Yii2 querybuilder sum with condition

0 Answers0