Multi sum with condition in single query

Hello,

I'm trying to get sum of amount with different different types in where condition as show below:

foreach ($yearMonths as $yearMonth) {
            foreach ($yearMonth as $year => $month) {
                $result = DailyTransaction::select([
                    'transMonth' => function ($query) {
                        $query->select(DB::raw("MONTH(transaction_date)"));
                    },
                    'transYear' => function ($query) {
                        $query->select(DB::raw("YEAR(transaction_date)"));
                    },
                    'totalSales' => function ($query) {
                        $query->select(DB::raw('SUM(amount)'))->where('action_types', 0);
                    },
                    'totalPurchase' => function ($query) {
                        $query->select(DB::raw('SUM(amount)'))->where('action_types', 1);
                    },
                    'totalExpanse' => function ($query) {
                        $query->select(DB::raw('SUM(amount)'))->where('action_types', 2);
                    },
                    'totalReturns' => function ($query) {
                        $query->select(DB::raw('SUM(amount)'))->where('action_types', 3);
                    },
                    'totalSalary' => function ($query) {
                        $query->select(DB::raw('SUM(amount)'))->where('action_types', 4);
                    }
                ])
                ->whereMonth('transaction_date', $month)
                ->whereYear('transaction_date', $year)
                ->groupBy('transMonth', 'transYear')
                ->get();
                return $result;
            }

And this result is

[▼
        "transMonth" => 4
        "transYear" => 2023
        "totalSales" => 256710.0
        "totalPurchase" => null
        "totalExpanse" => null
        "totalReturns" => null
        "totalSalary" => null
      ]

Getting incorrect totals. What I'm doing wrong here.

tisuchi

3 years ago

Best Answer

Level 70

@v9द Have you tried with subquery?

For example:

$result = DailyTransaction::select(
    DB::raw('MONTH(transaction_date) AS transMonth'),
    DB::raw('YEAR(transaction_date) AS transYear'),
    DB::raw('SUM(CASE WHEN action_types = 0 THEN amount ELSE 0 END) AS totalSales'),
    DB::raw('SUM(CASE WHEN action_types = 1 THEN amount ELSE 0 END) AS totalPurchase'),
    DB::raw('SUM(CASE WHEN action_types = 2 THEN amount ELSE 0 END) AS totalExpanse'),
    DB::raw('SUM(CASE WHEN action_types = 3 THEN amount ELSE 0 END) AS totalReturns'),
    DB::raw('SUM(CASE WHEN action_types = 4 THEN amount ELSE 0 END) AS totalSalary')
)
    ->where(function ($query) use ($yearMonths) {
        foreach ($yearMonths as $year => $months) {
            $query->orWhere(function ($query) use ($year, $months) {
                $query->whereYear('transaction_date', $year)
                    ->whereIn(DB::raw('MONTH(transaction_date)'), $months);
            });
        }
    })
    ->groupBy('transMonth', 'transYear')
    ->get();

return $result;

Ref: https://reinink.ca/articles/calculating-totals-in-laravel-using-conditional-aggregates

2 likes

V9द

3 years ago

Level 3

@tisuchi Thanks! Thats very helpful and really its work for me.

1 like

Please or to participate in this conversation.