Laravel: distinct() and count() give the total instead of the unique count
User::distinct()->count() returns the number of all rowsShort answer
Laravel builds the count from the column you pass to count(), not from select(). With distinct() and no column, the query is count(*), so duplicates are counted. Pass the column to count(), for example distinct()->count('city'). NULL values are not counted by COUNT(DISTINCT), which also explains a result lower than expected.
Fix it fast
1Find out which cause you have
Print the SQL Laravel builds for the count. If it says count(*) and not count(distinct ...), the column was never passed.
DB::enableQueryLog(); User::distinct()->count('city'); dd(DB::getQueryLog());2Match what you see, then jump to its fix
What does the error look like?
User::distinct()->count() returns the number of all rowsselect('city')->distinct()->count() ignores the selectThe distinct count is lower than the number of values you can see
Cause 1 of 2
No column was passed to count()
1Check
When the aggregate column is * the distinct flag adds nothing: the generated SQL is a plain count of rows. A column set with select() before count() is dropped: the builder clears the select columns before it adds the aggregate.
DB::enableQueryLog();
User::select('city')->distinct()->count();
dd(DB::getQueryLog());2Fix
Give the column to count(), which makes the builder write count(distinct city).
$cities = User::distinct()->count('city');
// same query written out
$cities = User::selectRaw('count(distinct city) as total')->value('total');3Verify
The log now shows count(distinct `city`) and the number matches the unique values.
DB::enableQueryLog();
User::distinct()->count('city');
dd(DB::getQueryLog());Confirmed by raw.githubusercontent.com
Cause 2 of 2
Rows with NULL are not counted
1Check
COUNT(DISTINCT expr) counts rows with different non-NULL values, so a NULL city never adds to the total. Passing several columns to distinct(), as in distinct('city', 'country')->count(), builds count(distinct city, country) and leaves out a row when any one of them is NULL.
User::whereNull('city')->count();2Fix
Count the NULL rows separately and add 1 if NULL should count as one value.
$unique = User::distinct()->count('city');
$hasNull = User::whereNull('city')->exists() ? 1 : 0;
$total = $unique + $hasNull;3Verify
Compare the result with a manual list of the distinct values.
User::select('city')->distinct()->get()->count();Confirmed by dev.mysql.com
How do I stop it coming back?
- Always pass the column to count() when you mean a unique count, and read the logged SQL once.
- Decide how NULL should count before you write the query, and document it in the code.
- Do not use get()->count() on big tables: it loads every row into PHP to count them.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- Spotless LaundryAn on-demand laundry app: Flutter on a Laravel back end, with Firebase for live updates.
- EZ EstimaterConstruction quoting and job tracking for contractors, on a Laravel and Firebase stack.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
Related fixes
Back end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fixBack end
Laravel Eloquent query for a column that is X or NULL
where('column', null) returns no rows
Wrap the pair in a closure: where(function ($q) { $q->where('col', $x)->orWhereNull('col'); }).
3 causes, each with a fixBack end
Laravel insert fails with Invalid datetime format 1292 Incorrect datetime value
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value
MySQL ran in strict mode and refused a date or time value that it cannot store.
3 causes, each with a fixBack end
php artisan migrate stops with could not find driver
could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)
Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.
3 causes, each with a fixBack end
php artisan migrate stops because the table already exists
SQLSTATE[42S01]: ... 1050 Table 'users' already exists
migrate tried to create a table the database already has.
2 causes, each with a fix