[laravel] Laravel Eloquent - distinct() and count() not working properly together

So I'm trying to get the number of distinct pids on a query, but the returned value is wrong.

This is what I try to do:

$ad->getcodes()->groupby('pid')->distinct()->count()

what returns the value "2", while the value it should return, should be "1".

As a workaround, I'm doing this:

count($ad->getcodes()->groupby('pid')->distinct()->get())

what works fine and returns "1"

Is there any rule where count and distinct cannot be on the same query? I find the workaround kind of "heavy", I would like to make the original query work :(

This question is related to laravel count eloquent distinct

The answer is


A more generic answer that would have saved me time, and hopefully others:

Does not work (returns count of all rows):

DB::table('users')
            ->select('first_name')
            ->distinct()
            ->count();

The fix:

DB::table('users')
            ->distinct()
            ->count('first_name');

Wouldn't this work?

$ad->getcodes()->distinct()->get(['pid'])->count();

See here for discussion..


try this

$ad->getcodes()->groupby('pid')->distinct()->count('pid')

$solution = $query->distinct()
            ->groupBy
            (
                [
                    'array',
                    'of',
                    'columns',
                ]
            )
            ->addSelect(
                [
                    'columns',
                    'from',
                    'the',
                    'groupby',
                ]
            )
            ->get();

Remember the group by is optional,this should work in most cases when you want a count group by to exclude duplicated select values, the addSelect is a querybuilder instance method.


I came across the same problem.

If you install laravel debug bar you can see the queries and often see the problem

$ad->getcodes()->groupby('pid')->distinct()->count()

change to

$ad->getcodes()->distinct()->select('pid')->count()

You need to set the values to return as distinct. If you don't set the select fields it will return all the columns in the database and all will be unique. So set the query to distinct and only select the columns that make up your 'distinct' value you might want to add more. ->select('pid','date') to get all the unique values for a user in a day


Distinct do not take arguments as it adds DISTINCT in your sql query, however, you MAY need to define the column name that you'd want to select distinct with. Thus, if you have Flight->select('project_id')->distinct()->get() is equialent to SELECT DISTINCT 'project_id' FROM flights and you may now add other modifiers like count() or even raw eloquent queries.


Anyone else come across this post, and not finding the other suggestions to work?

Depending on the specific query, a different approach may be needed. In my case, I needed either count the results of a GROUP BY, e.g.

SELECT COUNT(*) FROM (SELECT * FROM a GROUP BY b)

or use COUNT(DISTINCT b):

SELECT COUNT(DISTINCT b) FROM a

After some puzzling around, I realised there was no built-in Laravel function for either of these. So the simplest solution was to use use DB::raw with the count method.

$count = $builder->count(DB::raw('DISTINCT b'));

Remember, don't use groupBy before calling count. You can apply groupBy later, if you need it for getting rows.


You can use the following way to get the unique data as per your need as follows,

$data = $ad->getcodes()->get()->unique('email');

$count = $data->count();

Hope this will work.


I had a similar problem, and found a way to work around it.

The problem is the way Laravel's query builder handles aggregates. It takes the first result returned and then returns the 'aggregate' value. This is usually fine, but when you combine count with groupBy you're returning a count per grouped item. So the first row's aggregate is just a count of the first group (so something low like 1 or 2 is likely).

So Laravel's count is out, but I combined the Laravel query builder with some raw SQL to get an accurate count of my grouped results.

For your example, I expect the following should work (and let you avoid the get):

$query = $ad->getcodes()->groupby('pid')->distinct();
$count = count(\DB::select($query->toSql(), $query->getBindings()));

If you want to make sure you're not wasting time selecting all the columns, you can avoid that when building your query:

 $query = $ad->select(DB::raw(1))->getcodes()->groupby('pid')->distinct();

Based on Laravel docs for raw queries I was able to get count for a select field to work with this code in the product model.

public function scopeShowProductCount($query)
{
    $query->select(DB::raw('DISTINCT pid, COUNT(*) AS count_pid'))
          ->groupBy('pid')
          ->orderBy('count_pid', 'desc');
}

This facade worked to get the same result in the controller:

$products = DB::table('products')->select(DB::raw('DISTINCT pid, COUNT(*) AS count_pid'))->groupBy('pid')->orderBy('count_pid', 'desc')->get();

The resulting dump for both queries was as follows:

#attributes: array:2 [
  "pid" => "1271"
  "count_pid" => 19
],
#attributes: array:2 [
  "pid" => "1273"
  "count_pid" => 12
],
#attributes: array:2 [
  "pid" => "1275"
  "count_pid" => 7
]

This was working for me so Try This: $ad->getcodes()->distinct('pid')->count()


Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to count

Count the Number of Tables in a SQL Server Database SQL count rows in a table How to count the occurrence of certain item in an ndarray? Laravel Eloquent - distinct() and count() not working properly together How to count items in JSON data Powershell: count members of a AD group How to count how many values per level in a given factor? Count number of rows by group using dplyr C++ - how to find the length of an integer JPA COUNT with composite primary key query not working

Examples related to eloquent

Eloquent: find() and where() usage laravel How to select specific columns in laravel eloquent Laravel Eloquent where field is X or null Laravel Eloquent limit and offset laravel collection to array Eloquent get only one column as an array Laravel 5.2 - Use a String as a Custom Primary Key for Eloquent Table becomes 0 Laravel 5.2 - pluck() method returns array Eloquent ORM laravel 5 Get Array of ids eloquent laravel: How to get a row count from a ->get()

Examples related to distinct

Using DISTINCT along with GROUP BY in SQL Server How to "select distinct" across multiple data frame columns in pandas? Laravel Eloquent - distinct() and count() not working properly together SQL - select distinct only on one column SQL: Group by minimum value in one field while selecting distinct rows Count distinct value pairs in multiple columns in SQL sql query distinct with Row_Number Eliminating duplicate values based on only one column of the table MongoDB distinct aggregation Pandas count(distinct) equivalent