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）

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;
test data

INSERT INTO hc_members (id, name, amount) VALUES (1,'aaa',10.00), (2,'bbb',10.00), (3,'ccc',10.00);
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!

Because the Illuminate\Database\Eloquent\Collection offers a ->count() for a specific reason. — Ohgodwhy
– Ohgodwhy, Commented Jun 24, 2016 at 3:45
@Ohgodwhy But that doesn't seem to give correct results when combined with groupBy() — linuxartisan
– linuxartisan, Commented Jun 24, 2016 at 3:46
@aldrin27 thank you for your answer. As you said, I can use your method (but i get 500 error, maybe out of memory, get too many data, I think it's not necessary, I just need to get how many records it has) I have same proble with sum method,how can i solve it. — enbaby
– enbaby, Commented Jun 24, 2016 at 4:55

Rahul Govind · Accepted Answer · 2016-06-24 07:09:25Z

3

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

edited Jun 24, 2016 at 7:09

answered Jun 24, 2016 at 6:06

Rahul Govind

5895 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

enbaby Over a year ago

Thanks for your answer, How can I get the row num. sql: select count(*) from memebr group by id how to write in laravel

Rahul Govind Over a year ago

That's the query executed when you call Member::groupBy('id')->count(). If you want to get a count of the distinct ids, you can call Member::select('id')->distinct()->count('id'). (I'm assuming that's the result you require)

Rahul Govind Over a year ago

And whenever you call get(), you are essentially executing a query which returns a collection of the query results. This can usually consume all your memory and hang your application. On the other hand, until you call get() or a similar function, your query is just a query. It's not executed and you don't have to worry about a huge collection finishing off your memory.

Rahul Govind Over a year ago

You said you expect '30' but how do you expect to get that result? Do you wish to sum over the all amounts or something else, because that's the only way you can get that result in this case.

enbaby Over a year ago

thans for your professional answer. I'll edit my problem that why I have to use groupBy before count or sum method. Sorry for my ambiguous description.

Collectives™ on Stack Overflow

laravel use count/sum method after groupBy

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related