Group rows excluding column used in case

user_3416676 0
Viewed 47

I'm building a small stocks/portfolio tracker and I'm having some trouble retrieving and counting cells of my transactions. Below you can find the dummy data in my transactions table of my database.

// Transactions table
ID | Name       | Symbol      | Currency | amount_bought | amount_sold | price  | commission | bought | portfolio_id
_____________________________________________________________________________________________________________________

1  | Ocugen Inc | FRA.2H51    | EUR      |  55           | NULL        | 0.29   | 7.51       | 1      | 1
2  | Tesla, Inc | NASDAQ.TSLA | EUR      |  5            | NULL        | 654.87 | 4.23       | 1      | 1
3  | Ocugen Inc | FRA.2H51    | EUR      |  NULL         | 40          | 1.31   | 7.55       | 0      | 1

I'm using a boolean named bought (final column) in order to "identify" my transaction as either being a sold or bought stock. Next, I want to retrieve all my transactions using my portfolio_id and group them on their name, symbol and currency in order to output the following:

// Desired result:
Name       | Symbol      | Currency | amount_current | commission_total | bought_total | sales_total
_____________________________________________________________________________________________________

Ocugen Inc | FRA.2H51    | EUR      | 15             | 15.06            | 15.95        | 52.4
Tesla, Inc | NASDAQ.TSLA | EUR      | 5              | 4.23             | 3274.35      | 0

Currently my code works exactly like I wanted, except for how my rows are being grouped. Because I'm using a case, in order to calculate the total amount of buys and sales of a single stock, I'm forced to include the bought column into my groupBy(). Therefore my results are also grouped on the bought in addition to the name, symbol and currency:

// Current result:
name       | symbol      | currency | amount_current | commission_total | bought_total | sales_total
_____________________________________________________________________________________________________

Ocugen Inc | FRA.2H51    | EUR      | 15             | 7.51             | 15.95        | 0
Tesla, Inc | NASDAQ.TSLA | EUR      | 5              | 4.23             | 3274.35      | 0
Ocugen Inc | FRA.2H51    | EUR      | NULL           | 7.55             | 0            | 52.4

Below you can find my code that generates the result above.

$transactions = Transaction::where('portfolio_id', $portfolio->id)
    ->groupBy('name', 'symbol', 'currency', 'bought')
    ->select([
        'name',
        'symbol',
        'currency',
        DB::raw('sum(amount_bought) - sum(amount_sold) as amount_current'),
        DB::raw('sum(commission) AS commission_total'),
        DB::raw('case when bought = 1 then sum(price) * sum(amount_bought) else 0 end as bought_total'),
        DB::raw('case when bought = 0 then sum(price) * sum(amount_sold) else 0 end as sales_total')
    ])
    ->get();

How can I group my transactions on the stock name, symbol and currency and calculate their totals without grouping them on the bought column?

eloquent laravel
1 Answers

I don't really know Laravel, but you should be able to use:

$transactions = Transaction::where('portfolio_id', $portfolio->id)
    ->groupBy('name', 'symbol', 'currency')
    ->select([
        'name',
        'symbol',
        'currency',
        DB::raw('sum(amount_bought) - sum(amount_sold) as amount_current'),
        DB::raw('sum(commission) AS commission_total'),
        DB::raw('sum(case when bought = 1 then price * amount_bought else 0 end as bought_total'),
        DB::raw('sum(case when bought = 0 then price * amount_sold) else 0 end) as sales_total')
    ])
    ->get();

That is, remove bought from the group by and make the case the argument to sum().

user_11440350
Related