Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Laravel: distinct() and count() give the total instead of the unique count

ErrorUser::distinct()->count() returns the number of all rows

Short 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.

  • Applies to Laravel 12, MySQL 8.4, PHP 8.3
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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());
  2. 2Match what you see, then jump to its fix

    1. 1select count(*) as aggregate in the query log, and the same number as without distinct()No column was passed to count()Fix
    2. 2The distinct count is lower than the number of different values you see in the tableRows with NULL are not countedFix

What does the error look like?

  • User::distinct()->count() returns the number of all rows
  • select('city')->distinct()->count() ignores the select
  • The 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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Back 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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.