Sum of amount for each date having 'type' column in sql

user_9660462 0
Viewed 108

I have table expense_incomes, the structure for table is: 

| id | type    |  amount  |    date      |
|----------------------------------------|
|  1 | Expense |  100     |  2021-04-02  |
|  2 | Expense |  150     |  2021-04-02  |
|  3 | Expense |  150     |  2021-04-03  |
|  4 | Income  |  500     |  2021-04-04  |
|  5 | Expense |  250     |  2021-04-04  |
|----------------------------------------|

I want the sum of expenses and incomes in two different arrays groupBy date.
I have tried this code. (In the above Example) I want to push expense into expense_array and income into income_array for 2021-04-02.

$record_by_date = DB::select("SELECT SUM(amount) as 'amount', cast(date as date) dateAdded, type as 'type' FROM expense_incomes GROUP BY cast(date as date), type");
$expenses_array = array();
$incomes_array = array();
$date_array = array();
foreach($record_by_date as $record)
{
  if($record->type == 'Expense') {
      array_push($expenses_array, $record->amount);
      array_push($incomes_array, 0);
  } elseif($record->type == 'Income') {
      array_push($incomes_array, $record->amount);
      array_push($expenses_array, 0);
  }
  array_push($date_array, $record->dateAdded);
}

What I am getting now:

$expenses_array = [250, 150, 0, 250];
$incomes_array = [0, 0, 500, 0];
$date_array = [2021-04-02, 2021-04-03, 2021-04-04, 2021-04-04];

Expected Array Structure: 

$expenses_array = [250, 150, 250];
$incomes_array = [0, 0, 500];
$date_array = [2021-04-02, 2021-04-03, 2021-04-04];
laravel mysql sql php
2 Answers

I believe you want an aggregated query with conditional sum of amount for each date.

In pure SQL it can done using CASE statements as

select  date as dateAdded,
        sum(case when type='Income' then amount else 0 end) as income,
        sum(case when type='Expense' then amount else 0 end) as expense
from expense_incomes 
group by date 
order by date

DEMO

In query builder it can be transformed as

$data= DB::table("expense_incomes")
            ->select([ "date as dateAdded",
                        DB::raw("sum(case when type='Income' then amount else 0 end) as income"),
                        DB::raw("sum(case when type='Expense' then amount else 0 end) as expense")
            ])
            ->groupBy("date")
            ->orderBy("date")
            ->get();

Now you can use collection helper pluck to extract data for your columns

$expenses_array = $data->pluck('expense');
$incomes_array = $data->pluck('income');
$date_array = $data->pluck('dateAdded');
user_8533600 
$expensesArray =  DB::select("SELECT SUM(amount) as 'amount', cast(date as date) dateAdded, type as 'type' FROM expense_incomes GROUP BY cast(date as date), type where type = 'Expense'")->toArray();

$incomeArray =  DB::select("SELECT SUM(amount) as 'amount', cast(date as date) dateAdded, type as 'type' FROM expense_incomes GROUP BY cast(date as date), type where type = 'Income'")->toArray();
user_85387320
Related