Error with SUM function using distinct

[DavidVaras]

Laravel Version

12.8.1

PHP Version

8.3.28

Database Driver & Version

mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Description

If your query uses DISTINCT and you want to find the sum of a field, such as an amount, when you execute the function $elementos->sum('importe'), it will perform a DISTINCT operation on the amounts and sum them, instead of summing all the amounts across the records.

Real-world example

id	Proyecto	Importe
1	P001	260
2	P002	1362
3	P003	1263
4	P004	1263
5	P005	1263
6	P006	1263
7	P007	10
8	P008
9	P009	101
10	P010	101
	Total	6886

This query uses DISTINCT, and the result is shown above. If you run $elementos->sum(DB::raw('IFNULL(importe,0)'));

A filter is applied to the different amounts in the result; it doesn't use all the rows displayed. This means that when you have identical amounts, only one of them is summed.

The result of that instruction would be:

	Importe
	260
	1362
	1263
	10
	101
Total	2996

From my point of view it is a mistake because when you ask for the sum of the records you want the total sum and not just the different ones.

Thanks in advance.

Steps To Reproduce

Create an Eloquent query with multiple fields, using the DISTINCT clause in the SELECT clause, and one of the fields being an amount field. There must be multiple amounts with the same value within the selected records.

$elementos = Reclamacion::whereNull('reclamacion.deleted_at');
$elementos->select('reclamacion.*')->distinct();
$elementos->sum(DB::raw('IFNULL(importe,0)'));


[macropay-solutions]

This is expected behavior. You need to remove:

$elementos->select('reclamacion.*')->distinct();

[shaedrich]

Yeah, if one wants the sum of all one shouldn't use distinct—why would one anyway in such a case?

[macropay-solutions]

You can set distinct to false and after sum reset it to the initial value.

[DavidVaras]

I need DISTINCT because the original query has many joins, and the query selects many fields.

The distinct option should apply to the query itself, but it shouldn't affect the SUM function of that query at all.

The sum function should only perform the sum of the amounts from the result of the original query, not recalculate the records of that query.

If you run a SQL SUM query, it doesn't group by amounts, but by all fields in the query, so in this case it would leave all records.

[DavidVaras]

You can set distinct to false and after sum reset it to the initial value.

In that case, there would be more results than I need, because of the joins.

[shaedrich]

This probably isn't a Laravel but an SQL issue. Could you run ->ddRawSql() on the query and share the result with us?

[DavidVaras]

->ddRawSql()

This is the result of ddRawSql()

select
sum(distinct IFNULL(iva), 0)
)
) as aggregate
from
reclamacion
where
reclamacion.deleted_at is null
and reclamacion.reclamar_a = Instalador
and reclamacion.asignado_empresa in (615, 908, 909)

[iazaran]

@DavidVaras ddRawSql explains the behavior.
When you call ->distinct() on Laravel's query builder, it will be just set on the builder. When you then call ->sum(), Laravel's aggregate method generates SUM(DISTINCT column) as ddRawSql shows.

This is intentional in Laravel.

What you want: SELECT DISTINCT * FROM table then sum
What Laravel does: SELECT SUM(DISTINCT column) which will be sum unique values only

Solution can be what @macropay-solutions mentioned or more readable version which shows what you want first then what you want next.

[DavidVaras]

@DavidVaras ddRawSql explains the behavior. When you call ->distinct() on Laravel's query builder, it will be just set on the builder. When you then call ->sum(), Laravel's aggregate method generates SUM(DISTINCT column) as ddRawSql shows.

This is intentional in Laravel.

What you want: SELECT DISTINCT * FROM table then sum What Laravel does: SELECT SUM(DISTINCT column) which will be sum unique values only

Solution can be what @macropay-solutions mentioned or more readable version which shows what you want first then what you want next.

If you use get() to sum the amounts in the rows, the result is different than if you use the SUM() function on the same query.

In my humble opinion, you should never use DISTINCT to calculate the total in a sum; it doesn't make sense that if two rows have the same value they aren't summed, much less that this is a generalized calculation method for all the rows in a query.

For the calculation, I'm simply using a foreach loop in PHP.

This ensures that the totaled values ​​match the values ​​returned by the query and those displayed on the screen.

[iazaran]

@DavidVaras,
You are right, but I think it is better to play with Laravel queries like this by using both get and sum, or any others which provide the expected result:

$total = Model::distinct()
    ->get(['column'])
    ->sum('column');

Using PHP on the large data, will cause less efficient to loads all rows into memory.

[DavidVaras]

Okay, thank you all very much. I thought that was how it was supposed to work.