Using groupBy() to merge the Incomes of the same year

I am looking to get an array of the amounts of the income of all the existing years.

Here's the Income table:

public function up()
    {
        Schema::create('income', function (Blueprint $table) {
            $table->id();
            $table->string('description');
            $table->bigInteger('amount');
            $table->date('date');
            $table->string('receipt')->nullable();
            $table->foreignId('user_id')->nullable()->constrained('users')->onDelete('cascade');
            $table->timestamps();
        });
    }

Then the Eloquent queries to get the Years existing in the date column:

$years = Income::selectRaw('YEAR(income.date) AS year')->orderByRaw('date ASC')->groupBy('year')->pluck('year');
        $array = (array) $years;

$array gives the result = [2010,2012,2018,2019,2020,2021,2022]

But this is where there's an issue:

foreach ($years as $year){
            $yearlyIncome = Income::select('amount',DB::raw('YEAR(income.date) as year'))->orderByRaw('year ASC')->groupBy('amount','year')->pluck('amount');
        }

I get $yearlyIncome = [3000,450,2000,6800,500,2800,4000,1500,5000] So the incomes belonging to the same years have not been added together, how can i correct it ?

undeportedmexican

3 years ago

Level 15

I t hink you need to sum the amount, that's why it's not being grouped.

Income::select(DB::raw(sum(income.amount) as amount),DB::raw('YEAR(income.date) as year'))->orderByRaw('year ASC')->groupBy('year')->pluck('amount')

And then just group for year, not year and amount.

Mar55

3 years ago

Level 1

@undeportedmexican Thanks for your help, it's working !

But i get the data in the form of strings in the array $yearlyIncome = ["3000","450","2000","6800","3300","5500","5000"], while i do need integers, do you know how can change it ?

undeportedmexican

3 years ago

Level 15

@Mar55 Weird, I would assume it would produce Integers. I would map over the results, to cast them as Int or float (whichever you need):

Income::select(
	DB::raw(sum(income.amount) as amount),
	DB::raw('YEAR(income.date) as year')
)
	->orderByRaw('year ASC')
	->groupBy('year')
	->map(function($row){
			return [
				'year' => $row->year,
				'amount' => intval($row->amount) // Or floatval()
			]
	})

That should give you an array of all the items, as you need it.

Please or to participate in this conversation.