Posted By
Bajarangi soft ,
Posted On 23-09-2020
To count, max, min, avg, and sum of the data in table
SQL count() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SQL min() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
SQL avg() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SQL sum() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
To learn more about SQL, please visit our BajarangiSoft site.
How can you use aggregates function in laravel
Let's see
The query builder also provides a variety of aggregate methods such as count, max, min, avg, and sum. You may call any of these methods after constructing your query:
Example(1)
Counts the number of user in users table
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;//include this
class UserController extends Controller
{
public function index()
{
$users = DB::table('users')->count();//counts number of users
return view('user.index', ['users' => $users]);
}
}
?>
Example(2)
Get the maximum salary of employee
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;//include this
class UserController extends Controller
{
public function index()
{
$employee = DB::table('users')->max('salary');//get maximum number of salary
return view('user.index', ['employee' => $employee]);
}
}
?>
Example(3)
Get the minimum salary of employee
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;//include this
class UserController extends Controller
{
public function index()
{
$employee = DB::table('users')->min('salary');//get minimum number of salary
return view('user.index', ['employee' => $employee]);
}
}
?>
Example(4)
Get the sum of salary of the employee
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;//include this
class UserController extends Controller
{
public function index()
{
$employee = DB::table('users')->sum('salary');//get minimum number of salary
return view('user.index', ['employee' => $employee]);
}
}
?>
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;//include this
class UserController extends Controller
{
public function index()
{
$employee = DB::table('users')->avg('salary');//get minimum number of salary
return view('user.index', ['employee' => $employee]);
}
}
?>
You may combine these methods with other clauses:
$employee = DB::table('employee')->where('id', 1)->avg('salary');
Determining If Records Exist
Instead of using the count method to determine if any records exist that match your query's constraints, you may use the exists and doesntExist methods:
return DB::table('employee')->where('id', 1)->exists();
return DB::table('employee')->where('id', 4)->doesntExist();