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

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)

About this issue

Original URL
State: closed
Created 10 years ago
Reactions: 7
Comments: 23 (4 by maintainers)

Most upvoted comments

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

SELECT FOUND_ROWS()

jbroudou on Oct 18, 2017

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');

hopeseekr on May 31, 2018

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

shababhsiddique on Apr 8, 2018

I have the same issue like that

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

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

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

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 …

Johnmel15 on Nov 16, 2017

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

enbaby on Jun 23, 2016

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.

steve3d on Jun 12, 2016