how to use sum() in laravel

Question

I have a table total_count

id  studid  month   year     acls_id   total_p total_a 

1   30       08     2015        12        5      2      
2   35       08     2015        12        5      2      
3   52       08     2015        12        5      2      
4   53       08     2015        12        5      2  
5   54       08     2015        12        5      2  
6   55       08     2015        12        5      2  
7   30       09     2015        12        3      0  
8   35       09     2015        12        3      0  
9   52       09     2015        12        2      1  
10  53       09     2015        12        3      0  
11  54       09     2015        12        3      0  
12  55       09     2015        12        3      0

I want to calculate for each student total_p and total_a.

eg: studid = 30, total_p = 5 and total_a = 2.

So I'd like to get the total of each month for each studid and a sum of total_p and total_a for the total months.

My controller code is

$total_counts = DB::table('total_count')
                       ->whereBetween('smonth',08, 09))
                       ->whereBetween('syear', 2015, 2015))   
                       ->sum('total_p);
                       ->sum('total_a);

view blade.php

{{$total_counts->total_p}}
{{$total_counts->total_a}}

but it doesn't work..

How to use sum() in query builder format?

I would like an output like:

  studid     total_p   total_a

    30          8         2

    35          8         2

    52          7         3

    53          8         2

    54          8         2

    55          8         2

jedrzej.kurylo · Accepted Answer · 2015-08-19 14:09:51Z

2

Eloquent's aggregate function sum() returns a single scalar for all rows matching criterai. If you want to get a list of rows, you'll need to build a query that groups students by their ID and calculate the sums for each of them.

This query should do the trick:

$total_counts = DB::table('total_count')
  ->whereBetween('smonth',08, 09))
  ->whereBetween('syear', 2015, 2015))
  ->groupBy('studid')
  ->get(['studid', DB::raw('SUM(total_a) AS sum_a'), DB::raw('SUM(total_p) AS sum_p')]);

Each row in $total_counts will contain 3 values: studid, sum_a and sum_p.

answered Aug 19, 2015 at 14:09

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

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

3 Comments

cnbwd Over a year ago

Eg:studid 30 total_p = 5 and total_a= 2 ,so iam edit my attendance present become absent . so want decrement total_p by 1 and increment total_a by 1.

cnbwd Over a year ago

Iam using this code DB::table($wys_total_attend_table)->whereIn('studid',[$student->id]) ->where('smonth',$date_exploded[1]) ->where('syear',$date_exploded[2]) ->where('stotal_a','!=',0) ->increment('stotal_a', 1,['studid' => $student->id]) ->decrement('stotal_p', 1,['studid' => $student->id]);.but not work properly...

jedrzej.kurylo Over a year ago

I'm just checking your other question as we speak

Collectives™ on Stack Overflow

how to use sum() in laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related