Posted By
Bajarangi soft ,
Posted On 18-09-2020
SQL COUNT(), AVG() and SUM() Functions.
COUNT() function returns a counted rows value.
AVG() function returns a average value .
SUM() function returns a total sum value.
Syntax for SQL COUNT() Functions.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Syntax for SQL AVG() Functions.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Syntax for SQL SUM() Functions.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Lets learn
1.Create database and table
Student table
here we have student table for this table we need to use SQL COUNT() ,AVG() and SUM() functions.
| StudentID | StudentName | Address | DOB | Section |
Branch |
Percentage | Fee |
1 |
Liam | 23 MG road Banglore |
12/06 | A | CSE | 66 | 66,000 |
| 2 | Andrew | 2415 Anthony market str | 01/02 | A | ISE | 80 | 65,000 |
| 4 | Ryan | 18 Krishna sq. Banglore |
26/12 | B | EE | 67 | 70,000 |
| 5 | Charles | 878 Eli Berguvsvägen 8 | 23/11 | C | ECE | 52 | 75,000 |
Example for COUNT() function.
SELECT COUNT(StudentID) AS NumberofRows FROM Student;
Selecting student ID and counting all ID of student as Number of Rows from student table.
Example for AVG() function.
SELECT AVG(Percentage) AS Averagepercentage FROM Student
Selecting Percentage and averaging percentage of students as AveragePercentage from student table.
Example for SUM() function.
SELECT SUM(Fee) AS Sumoffee FROM Student
Selecting Fee and sumof the fee of all students as sumoffees from student table.
2.Complete code of SQL COUNT(), AVG() and SUM() Functions in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL COUNT(), AVG() and SUM() Functions</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<br>
<div class="text-center">
<h1>SQL COUNT(), AVG() and SUM() Functions</h1>
</div>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql_1 = "SELECT COUNT(StudentID) AS CountedRows FROM Student";
$sql_2= "SELECT AVG(Percentage) AS Averagepercentage FROM Student";
$sql_3= "SELECT SUM(Fee) AS Sumoffee FROM Student";
if($result_1 = mysqli_query($connect, $sql_1)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>CountedRows</th>
</tr>";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>" . $row['CountedRows'] . "</td></tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo"<br>";
if($result_2 = mysqli_query($connect, $sql_2)){
if(mysqli_num_rows($result_2) > 0){
echo "<table class='table'>
<tr>
<th>Averagepercentage</th>
</tr>
";
while($row = $result_2->fetch_assoc()){
echo "<tr>
<td>" . $row['Averagepercentage'] . "</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo"<br>";
if($result_3 = mysqli_query($connect, $sql_3)){
if(mysqli_num_rows($result_3) > 0){
echo "<table class='table'>
<tr>
<th>Sumoffee </th>
</tr>
";
while($row = $result_3->fetch_assoc()){
echo "<tr>
<td>" . $row['Sumoffee'] . "</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
</div>
<br>
</div>
</body>
</html>