SQL COUNT, AVG, SUM Functions | Aggregate Functions in SQL

Aggregate functions in SQL

SQL provides us features to make group of rows using GROUP BY and HAVING clauses and perform some calculations or operations on group of rows and return single value or row. An Aggregate function takes a group of values, one from each row in a group of rows, and return on value as output. COUNT, AVERAGE and SUM are most commonly used aggregate functions of SQL.

When we execute functions on same set of rows, every time the functions will return same value that is all aggregate functions are deterministic. Aggregate functions are most commonly used with SQL select statement.

Expect SQL COUNT functions, all other aggregate functions ignore null values, when we used count function with SQL select statement it consider null value records also while counting for specific conditional group of rows.

 

SQL COUNT Function

The SQL COUNT function used to perform counting on a group of rows and returns counting value in integer number for found rows or values which matches specified condition including duplicate values and null values. SQL COUNT is one of the most widely used aggregate functions. The SQL COUNT function is a deterministic function of SQL.

 

SQL COUNT Function Syntax

COUNT (Expression|Column_name [*] [ALL | DISTINCT])

• Expression|Column_name: An expression of any type, except image, ntext, or text, or a column name of the table on which counting will be performed.
• * :  Specifies to count all rows in the specified table or each group. COUNT (*) takes no parameters and does not support the use of DISTINCT. COUNT (*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT (*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
• ALL | DISTINCT: Specifies how to count rows where expression has a non-null value
• ALL (default): Counts all rows where expression evaluates to a non-null value.
• DISTINCT: Counts all rows where expression evaluates to a distinct non-null value.

 

Considering Hospital management system database for examples of the SQL aggregate functions
Patient Table

Doctor Table

Bill Table

Laboratory Table

 

SQL COUNT Rows Example

To count the number of doctors in a hospital use the SQL COUNT function

SELECT COUNT(doctor_id) AS 'Total Doctors'
FROM  doctor;

• The previous query will count for the number of records in the doctor table with NotNULL values in doctor_id column.

OUTPUT:

 

SQL COUNT(*) Rows with WHERE clause example

Use SQL COUNT function to count the number of patients having age more than 40

SELECT COUNT(*) AS 'Age More than 40'
FROM  patient
WHERE age > 40)

• In the above query, we used * as COUNT Function parameter to count for all rows where the age value is more than 40
• SQL select statement with Where clause is used to make a group of rows of patients based on condition

OUTPUT:

 

SQL COUNT(DISTINCT Column_name) example

To count the number of district cities where doctors are living using SQL COUNT Function

SELECT COUNT(DISTINCT address) AS 'Doctor City'
FROM  doctor

• In the above query, we used the DISTINCT keyword with address column name for not repeating the same address city name twice

OUTPUT:

 

SQL COUNT function with GROUP BY column_name

To count the number of patients admitted under each doctor use SQL COUNT Function

SELECT doctor.name, COUNT(patient.patient_id) AS 'total patient'
FROM  doctor INNER JOIN
patient ON patient.doctor_id = doctor.doctor_id
GROUP BY doctor.name

• In the above query, we join two tables doctor and patient with INNER join
• To make group each doctor-patient we used GROUP BY clause with the doctor name
• We applied the COUNT function with patient id as we want to find the number of patients

OUTPUT:

 

SQL COUNT Function with GROUP BY & HAVING clause example

Use SQL COUNT function to retrieve doctor name with a number of patients admitted under particular doctor if the number of admitted patient count is more than 1

SELECT doctor.name, COUNT(patient.patient_id) AS 'total patient'
FROM  doctor INNER JOIN
patient ON patient.doctor_id = doctor.doctor_id
GROUP BY doctor.name
HAVING (COUNT(patient.patient_id) > 1)

• In the above query, we first join to tables patient and doctor to make group records from both
• We use the GROUP BY clause to make a group of doctors counts patients admitted under that doctor
• We applied the count function on patient id to count patients and for the conditional count, we used the HAVING clause to count function return value is greater than 1.

OUTPUT:

 

SQL Average Function

SQL Average is a commonly used SQL Aggregate function used to calculate the mean or average value of a group of values or expressions returned from the SQL Select statement.it calculates the average value of a numerical dataset. The resulting value of AVG() is the arithmetic mean of a set of values contained in a specified field on a query.

 

SQL Average function Syntax

AVG (Expression|Column_name [ALL | DISTINCT])

ALL: Applies the aggregate function to all values. ALL is the default.

DISTINCT: Specifies that AVG operates only on one unique instance of each value, regardless of how many times that value occurs.

Expression | Colum_name: An expression is a numeric set of values, column_name is a table column name that has Integer or Numeric data type except for the bit data type. Aggregate functions and subqueries are not permitted as expressions with the AVG function.

 

SQL AVG Function with column name example

To find the average age of patients use the SQL AVG function

SELECT AVG(age) AS 'Avarage Age'
FROM  patient

• In the above query, we have applied the AVG function on age to find the mean value of age.

OUTPUT:

 

SQL AVG function with DISTINCT Keyword

Use SQL AVG function to find average laboratory bill amount without counting same amount twice

SELECT AVG(DISTINCT amount) AS 'Avarage Amount'
FROM  laboratory

OUTPUT:

To see the Difference in average value without using the DISTINCT keyword on same above example

SELECT AVG(amount) AS 'Avarage Amount'
FROM  laboratory

OUTPUT:

 

SQL Average function on column values and NULL values

 Null values are not considered in the calculation of the AVG function so we taking one example of this issue.

Using SQL AVG function calculates average room charges

SELECT AVG(room_charge) AS 'Avarage room charges'
FROM  bill

In the output of the above query, we can see that the null value in the room charges with billno 5010 , but it did not consider in calculation

OUTPUT:

 

SQL Average function usage with GROUP BY statement

SQL Group by Clause is mainly used with SQL Aggregate functions to make group of rows based on specific condition.

SQL AVG function can also be used with SQL Group by clause to do calculations on the same group of rows and to find an average value.

For example, use SQL AVG function to calculate average doctor charges for each patient

SELECT patient_id, AVG(doctor_charge) AS 'Avarage doctor charges'
FROM  bill
GROUP BY patient_id

• In the previous query group by clause is used to make a group of each patient
• On each group of patients, AVG function is applied to find average doctor chargers patient wise

OUTPUT:

 

SQL SUM Function

SQL SUM function is one of the commonly used SQL aggregate functions used to calculate the sum of all given values or expressions. SUM function is applied on only numeric columns. SUM function ignores NULL values in the calculation.

 

SQL SUM Function Syntax

SUM (Expression|Column_name [ALL | DISTINCT])

• ALL: Applies the aggregate function to all values. ALL is the default.
• DISTINCT: Specifies that AVG operates only on one unique instance of each value, regardless of how many times that value occurs.
• Expression | Colum_name : An expression is a numeric set of values, column_name is a table column name that has Integer or Numeric data type except for the bit data type.

 

SQL SUM function Return type

 

SQL SUM function Examples

To find the total amount bill amount of laboratory use the SQL SUM function

SELECT SUM(amount) AS 'Total Amount'
FROM laboratory

The above query is a simple example of the SQL SUM function to calculating the total bill amount we applied the SUM function on the amount column.

 

SQL SUM function(column_name) with WHERE condition

Calculate the total of laboratory chargers of patients having age more than 40

SELECT SUM(laboratory.amount) AS 'Total Amount'
FROM  laboratory INNER JOIN
patient ON laboratory.patient_id = patient.patient_id
WHERE (patient.age > 40)

• In above query used where conditional clause with select statement to select rows which satisfied this condition
• We applied SUM function on set of rows which selected by where clause to calculate total amount

OUTPUT:

 

SQL SUM function with Group BY clause and INNER JOIN

Calculate total room charges paid by each patient with patient id and patient name

SELECT bill.patient_id, patient.name, SUM(bill.room_charge) AS 'Total room charges'
FROM   bill INNER JOIN
patient ON bill.patient_id = patient.patient_id
GROUP BY bill.patient_id, patient.name

• In previous query group by clause make set of rows of each patient
• On each group of patients, the SUM function calculates the total of room charges

OUTPUT:

 

Summary

In this article, we have covered the most commonly used SQL Aggregate functions COUNT, SUM, and AVG with practical examples. In the first part, we cover the use of SQL COUNT Function, Syntax, and different examples with where and group by clauses. In the second part of this article, we have explained the SQL AVG function which is used to calculate the Average of a given set of values with syntax and examples, in the third part of this we have introduced the SQL SUM function to calculate the total of given argument values with the condition Where clause and Group by clauses.

 

References

SQL Aggregate Function