How To Use SQL COUNT, AVG And SUM Functions In PHP

admin_img Posted By Bajarangi soft , Posted On 18-09-2020

SQL aggregate functions are inbuilt functions that are used for performing various operations in data.SQL COUNT will returns the number of rows that matches a specified condition.SQL AVG will returns the average value of a numeric column.SQL SUM returns the total sum of a numeric column.

count_avg_sum

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>

Related Post