CODEWITHSUNDEEP
phpmysqllaravel
user71959
user71959

Reputation:

Total sum of database field value with where condition in Laravel

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.

Upvotes: 1

Views: 5319

Answers (2)

Gajendra Singh Rajput
Gajendra Singh Rajput

Reputation: 100

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;

Upvotes: 0

jkavalik
jkavalik

Reputation: 1316

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)

Upvotes: 2

Related Questions