laravel use count/sum method after groupBy

Question

When I use count or sum method, I get the wrong result.

For example:

Member::groupBy('id')->count()

I only get result 1, in another method

Member::count()

I get the right result.

I get wrong result when use sum method.

https://github.com/laravel/framework/issues/14123

Forgot to say, My laravel version info:

Laravel Framework version 5.1.40 (LTS)

Actually problem in project

I have a recharge log table.

1.pay log table

CREATE TABLE pay_logs (
   id int(11) NOT NULL AUTO_INCREMENT,
   amount decimal(20,2) DEFAULT '0.00',
   pay_sn varchar(20) DEFAULT NULL,
   join_date int(11) unsigned DEFAULT '0',
   PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.1 pay log talbe data

INSERT INTO pay_logs (id, amount, pay_sn, join_date)
VALUES
  (1,10.00,'aabbcc',1466753291),
  (2,10.00,'aabbcc',1466753292),
  (3,20.00,'bbccdd',1466753292),
  (4,30.00,'ccddee',1466753292);

2.description

In pay_log table, the 1 and 2 record is same. So when wants to filter the results by the actually recharge success time, I just need one record, so I use groupBy operation.

My wrong operation

DB::table('pay_log')
 ->whereBetween('joinDate',[$beginToday,$endToday])
 ->where('isSucceed','=',1)
 ->where('type','=',1)
 ->groupBy('pay_sn');
 ->count();

Finally,solve it.

$query = DB::table('pay_log')
 ->select(DB::raw('count(*) as num'))
 ->whereBetween('joinDate',[$beginToday,$endToday])
 ->where('isSucceed','=',1)
 ->where('type','=',1)
 ->groupBy('pay_sn');

ps: if use Eloquent ORM

modify ->mergeBindings($query) to ->mergeBindings($query->getQuery())

Test Example（discard）

1. create table sql

   CREATE TABLE members ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(15) DEFAULT NULL, amount decimal(20,2) DEFAULT '0.00', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

2. test data

   INSERT INTO hc_members (id, name, amount) VALUES (1,'aaa',10.00), (2,'bbb',10.00), (3,'ccc',10.00);

3. test result

In my hope, when I use the method Member::groupBy('name')->count()

I want result 3

actually it returns 1.

when I use the method Member::groupBy('name')->sum('amount')

I want result 30.00

actually it returns 10.00

Thanks for your answer to help me solve the problem!

Solution

That's not an issue with laravel because Member::groupBy('id')->count() will return the count of number of results for each id. (Laravel is returning only the first row from the query result.)

Try running this query

SELECT COUNT(*) AS aggregate FROM members GROUP BY 'id'

It will return a count of the number of rows for each (id) aggregate. This is the query executed when you call Member::groupBy('id')->count() and laravel, expecting only one row, returns the count of the first aggregate.

If you want a count of the distinct ids, you can call

Member::distinct('id')->count('id')

This is the same reason your sum query also returns 10. The query returns the sum of 'amount' for a group with the same 'name'.

To get the sum of all amounts, simply call,

Member::sum('amount');