Sum of amount for each date having ‘type’ column in sql

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];

Answer

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');