Is there possible to use group by with case in Laravel and Postgres?-CodePudding

Hello I have financial_transactions table which structure looks like

id | user_id | amount | type
____________________________
1  | 1       | 10     | 1
____________________________
2  | 1       | 20     | 2
____________________________
3  | 2       | 15     | 1
____________________________
4  | 2       | 10     | 1

I want to calculate the sum of amounts by type. type 1 is deposit and type 2 is withdraw. my expected output should be:

[
    [
        user_id => 1,
        totalDeposit => 10,
        totalWithdraw => 20
    ],
    [
        user_id => 2,
        totalDeposit => 25,
        totalWithdraw => 0
    ]

]

my Laravel Eloquent code looks like this:

   $data = FinancialTransaction::select([
           DB::raw("CASE WHEN (type = '1') THEN SUM(amount) ELSE 0 END AS totalDeposit"),
           DB::raw("CASE WHEN (type = '2') THEN SUM(amount) ELSE 0 END AS totalWithdraw"),
    ])->groupBy('user_id','type')
    ->get()

output is like this:

[
    [
        user_id => 1,
        totalDeposit => 10,
        totalWithdraw => 0
    ],
    [
        user_id => 1,
        totalDeposit => 0,
        totalWithdraw => 20
    ],
    [
        user_id => 2,
        totalDeposit => 25,
        totalWithdraw => 0
    ],

]

if I removing 'type' from group by then returns error

type must appear in the GROUP BY clause or be used in an aggregate function

thank you in advance. if you have any additional questions please comment and I will try to explain;

CodePudding user response：

I've checked this for Postgres and MySQL databases:

$data = FinancialTransaction::select([
        'user_id',
        DB::raw("SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) AS totalDeposit"),
        DB::raw("SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) AS totalWithdraw"),
    ])
    ->groupBy('user_id')
    ->get()
    ->toArray();

Additional notes for future usage:

Postgres

CREATE TABLE financial_transactions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    type INTEGER NOT NULL
);

INSERT INTO financial_transactions (user_id, amount, type) VALUES (1, 10, 1);
INSERT INTO financial_transactions (user_id, amount, type) VALUES (1, 20, 2);
INSERT INTO financial_transactions (user_id, amount, type) VALUES (2, 15, 1);
INSERT INTO financial_transactions (user_id, amount, type) VALUES (2, 10, 1);

MySQL

CREATE TABLE IF NOT EXISTS `financial_transactions` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `amount` int(11) NOT NULL,
    `type` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `financial_transactions` (`user_id`, `amount`, `type`) VALUES
(1, 10, 1),
(1, 20, 2),
(2, 15, 1),
(2, 10, 1);