SUM and AVG Functions in MySQL

MySQL contains several aggregate functions that are used for performing aggregate operations on specific column of a MySQL table. This article explains how we can calculate SUM and Average of all the values of the patient_age column of the patient table.

The SUM() function

SELECT SUM(column)
FROM table;

To add all the patient ages using SUM() function via MySQLi, pass the query for adding values to the query function of the mysqli object. The following example adds all values in the patient_age column.




Using SUM Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using SUM Function

$query = "SELECT SUM(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Sum of Ages is " . $row["SUM(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Using AVG() function in MySQL

SELECT AVG(column)
FROM table;

To calculate average of the patient ages using SUM() function in MySQLi, pass the query for averaging patient_age values to the query function of the mysqli object. The following example calculates average of all the values in the patient_age column.



Using Average Function in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using Average Function

$query = "SELECT AVG(patient_age) ".
"FROM Patient ";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "Average of Age " . $row["AVG(patient_age)"]."
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .