Total sum of database field value with where condition in Laravel

Question

I table student_atendence table fields are (id ,studid,attendence).

This is my table student_atendence:

studid attendence

28          1       
31          1       
32          1   
28          1   
31          1   
32          1   
28          1   
31          1   
32          1   
28          1   
31          0   
32          1   
28          0   
31          1   
32          1   
28          1   
31          1   
32          0   
28          1   
31          1   
32          0

I want result like this

id      studid     total 1's     total 0's   

1         28        6              1

2        31         6              1

3        32         5              2

How can I get the total count of attendence =1 and attendence=0 of each students separately?

Eg: 28 - 6 (total no.of 1 s) and 1(total o.of 0 s), 31 - 6 (total no.of 1 s) and 1(total o.of 0 s), 32 - 5 (total no.of 1 s) and 2(total o.of 0 s).

My controller code :

foreach($students as $student){
        $cunt1 = DB::table($wys_attendence_table)
                    ->where('studid',$student->id)
                  ->where('amonth',$amonth)
                ->where('ayear',$ayear)
                ->where('attendence','=',1)
                 ->count();
       $cunt0 = DB::table($wys_attendence_table)
                 ->where('studid',$student->id)
                  ->where('amonth',$amonth)
                ->where('ayear',$ayear)
                ->where('attendence','=',0)
                 ->count();
                 //var_dump($cunt1);
                //var_dump($cunt0);
        }

My view page:

@foreach($stud_attend as $stud_attends)
    @if($student->id == $stud_attends->studid)
        @if($stud_attends->attendence == 1)
            <td>
                <font color="green" size="3">p</font>
            </td>
        @elseif($stud_attends->attendence == 0)
            <td>
                <font color="red" size="3">a</font>
            </td>
        @endif
        <td>{{ $cunt1 }}</td>
        <td>{{ $cunt0 }}</td>
    @endif
@endforeach

I get the correct answer from var_dump($cunt0), var_dump($cunt1) but it does not work in the view page.

jkavalik · Accepted Answer · 2015-08-12 12:06:43Z

2

You can do it with conditional counts like this

http://sqlfiddle.com/#!9/9d3c1/2

SELECT
  studid,
  sum(attendence = 1) AS `total 1's`,
  sum(attendence = 0) AS `total 0's`
FROM
  student_atendence
GROUP BY
  studid;

I am not sure where you want to get that id you show in expected results as one studid can have many ids assigned in that table. If you want the smallest one, you can add min(id) AS id to select as in http://sqlfiddle.com/#!9/9d3c1/4 but it has not much real meaning.

I do not have experience with Laravel, but there seems to be a possibility to pass RAW expressions. Something like (just guessing here):

DB::table($wys_attendence_table)
->select(DB::raw('studid, sum(attendence = 1) AS `total1`, sum(attendence = 0) AS `total0`'))
->where('studid',$student->id)
->where('amonth',$amonth)
->where('ayear',$ayear);

(I changed the aliases to access it better in an array)

edited Aug 12, 2015 at 12:06

answered Aug 12, 2015 at 10:15

jkavalik

1,31611 silver badges22 bronze badges

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

2 Comments

cnbwd Over a year ago

how to transform this above code " SELECT studid, sum(attendence = 1) AS total 1's, sum(attendence = 0) AS total 0's FROM student_atendence GROUP BY studid; " into laravel db bulider code. i want in laravel code. its mysql code. how to change laravel

jkavalik Over a year ago

See edit - not sure it is 100% right, but something like this should work. It is supposed to return an array of 3 columns.

Gajendra Singh Rajput · Accepted Answer · 2015-08-12 13:16:47Z

0

You can do with if condition also

select id,studid,sum(if(attendence=1,1,0)) as total1 ,sum(if(attendence=0,1,0)) as total0 from student_atendence group by studid;

answered Aug 12, 2015 at 13:16

Gajendra Singh Rajput

1001 silver badge8 bronze badges

Collectives™ on Stack Overflow

Total sum of database field value with where condition in Laravel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related