How can we use count() with conditional sql in select() of laravel?

laravel db::select count
laravel eloquent group by
laravel subquery
laravel eloquent join
how to use count function in laravel
laravel raw query with parameters
laravel between
laravel withcount groupby

I have two tables as shown below. I am using Laravel DB method to join this table. But I am not getting how can I get the count of marks of students as per failed or passed. 0-failed 1-passed.

Expected result:

1. Student Name
2. Student Id,
3. Count of failed based on student Id as count_failed
4. Total Marks based on student Id as total_marks

table students

`+----+-----------+
 | id |   name    |
 +----+-----------+
 |  1 | John Doe  |
 |  2 | Mark P    |
 |  3 | Pen Henry |
 +----+-----------+`

table students_marks:

 +----+------------+-------+-----------+
 | id | student_id | marks |is_failed  |
 +----+------------+-------+-----------+
 |  1 |          1 |    55 |  0        |
 |  2 |          2 |    44 |  1        |
 |  3 |          1 |    11 |  1        |
 |  4 |          2 |    10 |  0        |
 |  5 |          2 |    11 |  1        |
 |  6 |          2 |    20 |  0        |
 +----+------------+-------+-----------+

Below is query which I used:

$users = DB::table('users')
        ->join('contacts', 'students.id', '=', 'students_marks.user_id')
        ->select('student.*')
        ->get();

I am unable to get how can we use count() with conditional SQL in select() of laravel?

Use conditional aggregation:

$users = DB::table('students s')
    ->leftJoin('students_mark sm', 's.id', '=', 'sm.user_id')
    ->groupBy('sm.id')
    ->select(DB::raw('s.id, s.name, SUM(sm.is_failed) AS num_failed, COUNT(sm.user_id) AS total_cnt'))
    ->get();

This corresponds to the following raw MySQL query:

SELECT
    s.id,
    s.name,
    SUM(sm.is_failed) AS num_failed,
    COUNT(sm.user_id) AS total_cnt
FROM students s
LEFT JOIN students_marks sm
    ON s.id = sm.user_id
GROUP BY
    s.id;

Note: It is acceptable in ANSI SQL to select the name field in the above GROUP BY query assuming that student#id is the primary key column for that table. If the above query gives an ONLY_FULL_GROUP_BY error, then simply add s.name to the GROUP BY clause.

Get all with count of specific condition., Another option would be to make use of DB::raw() - https://laravel.com/docs/5.5/​queries $select = DB::raw( 'COUNT(*) AS total , COUNT(CASE  Hi Guys, In this small article. i will let you know how to use select with count aggregate and group by using db raw in Laravel application. Here i gave you simple query example for join with count(*) as aggregate in select statement, i convert it into laravel db query builder.

 $users = DB::table('users')
                ->join('contacts', 'students.id', '=', 'students_marks.user_id')
                 ->select('student.*', DB::raw("count(students_marks.is_failed) as count")))
                 ->where('status', '=', 0)
                 ->get();

Try this. If this is not clear or doesn't work refer this

Calculating totals in Laravel using conditional aggregates , Calculating totals in Laravel using conditional aggregates to process all the records will be slow, and will use a ton of memory. select count(*) as total, count(case when status = 'confirmed' then 1 Unlike in PHP where false or null returns false , in SQL (and JavaScript for that matter) it returns null . The SQL COUNT function is an aggregate function that returns the number of rows returned by a query. You can use the COUNT function in the SELECT statement to get the number of employees, the number of employees in each department, the number of employees who hold a specific job, etc. The following illustrates the syntax of the SQL COUNT function: COUNT ( [ALL | DISTINCT] expression);

Try this code snippet

$table = DB::table('students_marks');
$table = $table->select(
        \DB::raw('SUM(if(is_failed = '0', 1, 0)) AS failed'), 
        \DB::raw('SUM(if(is_failed = '1', 1, 0)) AS passed'),
        \DB::raw('SUM(marks) AS total'));
        $table = $table->Join('students', 'students.id', '=', 'students_marks.student_id'); 
        $table = $table->get();

Database: Query Builder - Laravel, Ordering, Grouping, Limit & Offset; Conditional Clauses; Inserts; Updates uses PDO parameter binding to protect your application against SQL injection attacks. If you must allow the user to select certain columns to query against, always validate the You may use the table method on the DB facade to begin a query. C OUNT () with HAVING The HAVING clause with SQL COUNT () function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT () function. The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.

Try this

$users = DB::table('students s') ->leftJoin('students_mark sm', 's.id', '=', 'sm.student_id') ->groupBy('s.id','s.name') ->selectRaw("s.id,s.name,SUM(sm.is_failed) AS count_failed,SUM(sm.marks) as total_marks") ->get();

Query Builder - Laravel, It can be used to perform most database operations in your application, and works on binding throughout to protect your application against SQL injection attacks. Adding A Select Clause To An Existing Query The query builder also provides a variety of aggregate methods, such as count , max , min , avg , and sum . Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

Eloquent ORM - Laravel, Each database table has a corresponding "Model" which is used to interact with that table. Before getting started, be Note that we did not tell Eloquent which table to use for our User model. The SQL performed by this statement will be as follows: select * from users where id = 1 select * from phones where user_id = 1. Conditional aggregates. There's actually a really simple way to calculate these totals using a single database query. The trick is to put conditions within aggregate functions. Here's an example in SQL: select count(*) as total, count(case when status = 'confirmed' then 1 end) as confirmed, count(case when status = 'unconfirmed' then 1 end) as unconfirmed, count(case when status = 'cancelled' then 1 end) as cancelled, count(case when status = 'bounced' then 1 end) as bounced from subscribers

SQL COUNT() with HAVING, COUNT() with HAVING. The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING  SQL COUNT( ) with All In the following, we have discussed the usage of ALL clause with SQL COUNT() function to count only the non NULL value for the specified column within the argument. The difference between ‘*’(asterisk) and ALL are, '*' counts the NULL value also but ALL counts only NON NULL value.

Eloquent Trick: Group By Raw with Boolean Condition, $results = User::select('city', \DB::raw('COUNT(id) as amount')) ->groupBy('city') So, we're using DB::raw() for all select statement, also repeating the same You can use such grouping whenever you have clear condition of  We will learn how to use if condition in laravel select query. we can use mysql case when statement with db raw in laravel. If you want to use mysql function then you must have to use db raw function. in this example i will show you laravel select query with if condition. In minor case we need to use if else condition in laravel 5 Eloquent.

Comments
  • so you want to count of pass and fail marks?
  • I have 20 odd fields and I need to add this 20 odd fields into groupBy() else its giving me error of ONLY_FULL_GROUP_BY .@ Tim Biegeleisen
  • Also here we are unable to get failed count @Tim Biegeleisen
  • Then just make sure that every additional field beyond id which you add to the SELECT clause also appears in the GROUP BY clause. In any case, I have answered your original question.
  • I think we are missing the logic of count of ` SUM(sm.is_failed) AS num_failed` will sum but we need count
  • No, you don't, because is_failed is 1 when a failure occurred and zero otherwise, so taking the sum of this field per student gives the number of failures.