John Davidson

Message:

I have a query:

InvoiceLine::selectRaw('billable_name as name')

                ->selectRaw('cast(sum(quantity) as unsigned) as pcs')

                ->selectRaw('cast(sum((unit_price + environmental_tax) / 100) as unsigned) as sub_sum')

                ->where('billable_type', 'like', '%Product')

                ->groupByRaw('billable_name')

                ->orderBy('name')

                ->paginate(20)

I would like receive in every query the total sum of sub_sum.
Of course without paginate, I simply take the result and sum all sub_sum, but in this case it does not work.
My other idea is two query with union. In the second without paginate and others, only sum of sub_sum.
But maybe there is other way...

The result now:

name, pcs, sub_sum

name, pcs, sub_sum

...

I would like to get the sum of all sub_sums.

Thanks,

---

My Answer:
You can achieve this by using Laravel's query builder and the sum() method. Here's how you can modify your query to include the total sum of sub_sum:

php
$subquery = InvoiceLine::selectRaw('billable_name as name')
->selectRaw('cast(sum(quantity) as unsigned) as pcs')
->selectRaw('cast(sum((unit_price + environmental_tax) / 100) as unsigned) as sub_sum')
->where('billable_type', 'like', '%Product')
->groupByRaw('billable_name')
->orderBy('name');

$totalSubSum = InvoiceLine::selectRaw('sum((unit_price + environmental_tax) / 100) as total_sub_sum')
->where('billable_type', 'like', '%Product')
->first();

$results = $subquery->paginate(20);

// Access the total sum of sub_sum
$totalSum = $totalSubSum->total_sub_sum;

// Return the results and total sum
return [
'results' => $results,
'total_sum' => $totalSum
];


This way, you can get the individual results with pagination and also get the total sum of sub_sum in a separate query.