How can we use count() with conditional sql in select() of laravel?
laravel eloquent group by
laravel eloquent join
how to use count function in laravel
laravel raw query with parameters
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.
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
`+----+-----------+ | id | name | +----+-----------+ | 1 | John Doe | | 2 | Mark P | | 3 | Pen Henry | +----+-----------+`
+----+------------+-------+-----------+ | 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
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.
$users = DB::table('students s')
->leftJoin('students_mark sm', 's.id', '=', 'sm.student_id')
->selectRaw("s.id,s.name,SUM(sm.is_failed) AS count_failed,SUM(sm.marks) as total_marks")
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.
- 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
idwhich you add to the
SELECTclause also appears in the
GROUP BYclause. 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_failedis 1 when a failure occurred and zero otherwise, so taking the sum of this field per student gives the number of failures.