Framework: [Bug] Query Builder count() return wrong number with groupBy()

[wafgomes]

Using Query Builder count() with groupBy() doesn't return the correct number of rows, not considering the group by condition

I just made an example to show the problem

consider following table and code:

| id | cat | desc |
| --- | --- | --- |
| 1 | 1 | abc |
| 2 | 1 | abc |
| 3 | 2 | abc |

$count = DB::table('product')
    ->where('cat', 1)
    ->groupBy('cat')
    ->count();

var_dump($count);

$count = DB::table('product')
    ->where('cat', 1)
    ->groupBy('cat')
    ->get();

var_dump(count($count));

Output:

int(2)

int(1)

[taylorotwell]

Perhaps send a pull request to this repository?

[wafgomes]

nah, I'll let this open for who knows how to do that.

I can't run unit tests

[ecsos]

something like this?
in IlluminateDatabaseQueryBuilder.php

    /**
     * Retrieve the "count" result of the query.
     *
     * @param  string  $columns
     * @return int
     */
    public function count($columns = '*')
    {
        if ( ! is_array($columns))
        {
            $columns = array($columns);
        }

        if (!empty($this->groups)) {
            return count($this->select($this->raw('count(*) AS AGGREGATE'))->get());
        } else {
            return (int) $this->aggregate(__FUNCTION__, $columns);
        }
    }

[ebrahimradi]

I have this problem too.
What should I do?

[GrahamCampbell]

This is already fixed in laravel 5.

[ebrahimradi]

what should I do for 4.2 ?
Thanks

[Sladewill]

Its not difficult to upgrade to 5/5.1.

[mzur]

@GrahamCampbell I have a similar Issue with v5.1.16:

public function points()
{
    return $this->hasMany('Point')->orderBy('index', 'asc')->groupBy('id');
}

var_dump($annotation->points()->count()); // int(1)
var_dump(count($annotation->points()->get())); // int(2)

So points()->count() returns 1 although there should be two elements. Grouping by the ID was a workaround because otherwise I got an SQL error SQLSTATE[42803]: Grouping error: 7 ERROR: column "points.index" must appear in the GROUP BY clause or be used in an aggregate function when trying things like points()->max('index').

Edit: Never mind... I executed the resulting SQL query manually and it returns the count _for each group_ which is always 1 of course. Seems like I have to fix the problem with orderBy and max first.

[steve3d]

this is not fixed in 5.2.35

if I use $query->count() with a groupBy, this count will be the first item's count, not total count.

[enbaby]

@steve3d yeah,I have the same problem,when i use count method after groupBy method,I get 1,but actually it has 77.How to solve it.

[jbroudou]

This is still an issue with 5.5.16
I've had to get around this by running a separate query

SELECT FOUND_ROWS()

[MichaelVoelkel]

Also very confusing: If I have some model and call \App\SomeModel::findOrFail(100)->count(), I will get the number of elements of SomeModel in total, although 1 should be the intuitive answer.

[Johnmel15]

I have the same issue like that

I want to count the same name but it retrieve as the data below

This is my table datas as I labeled that what I want to be output

Instead of the result of the first picture I want to output like this but how i can do that ?

this is my code ..
$salaryRate = DB::table('alumni_tracers')
->select(DB::raw("count() as count"), 'grad.')
->join('graduate_per_courses as grad', 'grad.program', '=', 'alumni_tracers.course')
->leftjoin('graduate_per_courses as gradd', 'gradd.year', '=', 'alumni_tracers.year_graduated')
->groupBy('program')
->get();

hope you help me guys ..

[fico7489]

count() from eloquent completely ignores what is in select and just adds "count(*) as aggregate" to select part, but what should really generate is this :

select count(*) from (AND QUERY) as aggregate;

[shababhsiddique]

this has been buggin me for the past 3 hours, i was going nuts thinking something wrong in my search. Is there a workaround for this yet?

EDIT ya there is: you need to call ->get() before doing ->count(). if you dont call get before count it only counts the last group

[rs-sliske]

i dont think i would even call it a workaround. the way to get the result people seem to want is to use distinct rather than group by

[hopeseekr]

Here's the "eloquent" way to work around this issue:

            $realCount = DB::query()->selectRaw(
                    sprintf(
                        'COUNT(*) count FROM (%s) agg',
                        $mainQuery->select('id')->toSql()
                    ),
                    $mainQuery->getBindings()
                )->value('count');

[Jossnaz]

thanks @shababhsiddique

[themanish]

The problem still exists

[Nathanw]

Still exists.

[majebry]

Still exists

[fico7489]

Still exists.

[driesvints]

I can't even get the originally example to work properly. Please open up a new issue with all the information needed to re-create this: migrations, code, models, etc.