get sum of all the transactions of each user for specific month

Question

I am working on small finance web app and I need to generate one monthly general report of all the users which shows a total of all the transactions made by each user during a month. the table structure is as below.

username    amount  date
-----------------------------
a           1000    2018-8-1
b           2000    2018-8-1
c           1500    2018-8-3
b           1700    2018-8-6
b           1100    2018-8-7
a           2000    2018-8-10
c           1600    2018-8-12

Need to get sum of each user's transactions during the month such as

a : 3000
b : 4800
c : 3100

Please help me to find out the sum using laravel query builder.

Have you tried writing any Laravel code yet? Here is one SO answer which you can probably use right away: https://stackoverflow.com/questions/24887708/laravel-eloquent-sum-with-groupby — Tim Biegeleisen, Aug 23 '18 at 09:41
Possible duplicate of [Laravel Eloquent: sum with groupBy](https://stackoverflow.com/questions/24887708/laravel-eloquent-sum-with-groupby) — Jonas Staudenmeir, Aug 31 '18 at 17:20

Mtxz · Answer 1 · 2018-08-23T15:56:56.073

-1

You can do this :

$user
->transactions()
->where('date', '>', Carbon::before())
->where('date', '<', Carbon::now())
->get(['user_id', 'amount'])
->sum('amount');

This considering you have a relationship between transactions and user models. You can adapt to your schema. But the pure MySQL solution that @Tim Biegeleisen linked may be more resources efficient.

You may also use whereBetween(), but check that it works correctly with dates.

edited Aug 23 '18 at 15:56

answered Aug 23 '18 at 10:24

Mtxz

3,749
15
29

This does not specify a specific month. – GiamPy Aug 23 '18 at 11:14
Oups sorry, I forgot the date query. I updated my answer. Carbon dates are examples of course. – Mtxz Aug 23 '18 at 15:57

get sum of all the transactions of each user for specific month

1 Answers1