Help with SQL query

Hello,

I have a fairly nasty SQL query, which i'm not able to execute. So, i need to extract 2 fields for selected dates... and then the same 2 fields for just the last 6 months. I decided not to use Eloquent for the sake of ease.

Given the circumstances, i need to fit all this into ONE sql query.

Following is what i have right now :

select customerAccount, 
        count(id) as countIdAll ,
        sum(amount) as sumAmtAll,
        count(case when (updated_at >= date_sub(now(), interval 1 month) ) then id else 0 end) as countId6,
        sum(case when (updated_at >= date_sub(now(), interval 6 month) ) then amount else 0 end) as sumAmt6,
        count(updated_at) as countUpdate, 
        updated_at from transactions 
        WHERE updated_at BETWEEN "'.$from.'" AND "'.$to.'"
        group by  customerAccount order by customerAccount

As you can see, the countIdAll returns the number of times the ID is present The sumAmtAll returns the sum of amount The countId6 "should" return the count of ID for the last 6 months only And the sumAmt6 "should" return the sum of amount for the last 6 months only.

The countId6 and sumAmt6 are returning the same values as countIdAll and sumAmtAll... which is not what i want.

Any help is appreciated, thanks.

biishmar

8 years ago

Level 11

Transaction::whereBetween('updated_at', [$from, $to])
            ->selectRaw('count(id) as countIdAll ,sum(amount) as sumAmtAll,count(case when (updated_at >= date_sub(now(), interval 1 month) ) then id else 0 end) as countId6,sum(case when (updated_at >= date_sub(now(), interval 6 month) ) then amount else 0 end) as sumAmt6,
        count(updated_at) as countUpdate,')
        ->groupBy('customerAccount')
        ->orderBy('customerAccount');

Please or to participate in this conversation.