CODEWITHSUNDEEP
phplaravelcountgroup-bysum
enbaby
enbaby

Reputation: 33

laravel use count/sum method after groupBy

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!

Upvotes: 3

Views: 21201

Answers (1)

Rahul Govind
Rahul Govind

Reputation: 589

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

Upvotes: 3

Related Questions