Request a callback
Email us

SQL Aggregate Functions

  • Read Time5 mins

SQL aggregate functions are functions that perform calculations on multiple rows and return an aggregated single value. For instance, the SUM() aggregate function in SQL server adds all the values in a table column and returns the overall sum.

But for a better understanding of this SQL function, we’ll show you some examples of SQL aggregate functions in MS SQL Server. If you wish to consult a list of all the aggregate functions supported by MS SQL Server, it’s available on Microsofts’ official documentation page.

The following are some of the most commonly used aggregate functions and the ones mentioned in this article. 

  1. AVG
  2. MIN
  3. MAX
  4. SUM
  5. STDEV
  6. VAR
  7. COUNT
  8. DISTINCT (Not strictly an aggregate function)

Our SQL courses will teach you all of these functions and more in detail with the help of qualified trainers. Check our SQL training courses page for more information.

Creating Dummy Records

The script below creates a dummy database with one table. The table contains dummy records of 12 students. 

When you are creating a real SQL database, backups are important. Don’t forget to create a backup plan.

CREATE DATABASE School

USE School

CREATE Table Students (
 StudentID int,
 StudentName varchar(255),
 StudentGender varchar(255),
 StudentAge float,
 StudentDepartment varchar (255)
);


INSERT INTO Students(StudentID, StudentName, StudentGender, StudentAge, StudentDepartment)
VALUES (1, ‘Jack’, ‘Male’, 25, ‘English’),
(2, ‘Foster’, ‘Male’, 20, ‘History’),
(3, ‘Nick’, ‘Male’, 21, ‘Mathematics’),
(4, ‘Sara’, ‘Female’, 28, ‘English’),
(5, ‘Elizabeth’, ‘Female’, 29, ‘Mathematics’),
(6, ‘John’, ‘Male’, 22, ‘Mathematics’),
(7, ‘Elice’, ‘Female’, 20, ‘History’),
(8, ‘Ned’, ‘Male’, 30, ‘English’),
(9, ‘Maria’, ‘Female’, 32, ‘Mathematics’),
(10, ‘Anna’, ‘Female’, 24, ‘English’),
(11, ‘Andy’, ‘Male’, 25, ‘History’),
(12, ‘Josh’, ‘Male’, 35, ‘History’)

Let’s execute a SELECT statement to see the records in the dummy Students table that you just created.

SELECT * FROM Students

 

Records of the Students table.

 

Let’s now see some of the most commonly used aggregate functions in action.

AVG() Function

The AVG() function returns the average of all non-null values in a column. The following script returns the average age of all the students in the Students table.

SELECT AVG(StudentAge) as Average_Age
FROM Students

Table showing the average age: 25.92.

MIN() Function

The MIN() function returns the smallest non-null value from a column. For instance, the script below returns the minimum age from the StudentAge column.

SELECT MIN(StudentAge) as Min_Age
FROM Students

Table showing the minimum age: 20.

If the MIN() function is applied to a column containing character or string values, the MIN() function returns the first record from all the records sorted in alphabetical order.

For instance, the script below returns the first student name, when all the student names in the StudentName column are sorted in alphabetical order. 

SELECT MIN(StudentName) as Max_Age
FROM Students

Table showing one student name: Andy.

MAX() Function

The MAX() function, when applied to a numeric column, returns the highest value from the column. 

As an example, the script below returns the highest value from the StudentAge column.

SELECT MAX(StudentAge) as Max_Age
FROM Students

Table showing the highest value in the age column: 35.

The MAX() function, when applied on a VARCHAR type column, returns the first value in reverse alphabetical order.

When you are working with real databases, you will need to update the values of your database. Learn how to use SQL’s Update statement in this article.

SUM () Function

The SUM() function adds all the non-null values in a column. 

The following script returns the sum of all the values in the StudentAge column.

SELECT SUM(StudentAge) as Sum_Age
FROM Students

Table showing the sum of all ages: 311.

STDEV() Function

The STDEV() function finds standard deviation of values in a numeric column. Here is an example script that finds the standard deviation of the values in the StudentAge column.

SELECT STDEV(StudentAge) as STD_Age
FROM Students

Table showing the standard deviation: 4.91.

VAR() Function

The VAR() function returns the statistical variance of records in a column. Here is an example.

SELECT VAR(StudentAge) as VAR_Age
FROM Students

Table showing the statistical variation: 24.08.

COUNT() Function

The COUNT() function returns the count of non-null values in a column.

The following script returns the count of all the non-values in the StudentAge column.

SELECT COUNT(StudentAge) as TotalStudents
FROM Students

Since there are 12 records in the Students table and none of the records contains a null value in the StudentAge column, you can see 12 as the output of the COUNT() function.

Table showing the total number of records: 12.

DISTINCT() Function

The DISTINCT() function returns all the unique non-null values from a column. For example, the script below returns unique department names from the StudentDepartment column. 

SELECT COUNT(DISTINCT (StudentDepartment))
FROM Students

Table showing the different departments: English, History and Mathematics.

Multiple Aggregate Functions

You can also call multiple aggregate functions in a single SELECT statement. 

For instance, the script below uses the MIN() and MAX() aggregate functions with a single SELECT statement to return the smallest and highest value from the StudentAge column. 

SELECT MIN(StudentAge) as Min_Age, MAX(StudentAge) as Max_Age
FROM Students

Table showing the lowest and highest age: 20 and 35.

Grouping Aggregate Function Results With GROUP BY 

You can also group the result returned by an aggregate function. To do so, you need to use the GROUP BY statement.

As an example, the following script returns the average age of the students in the Students table, grouped by the department names which are stored in the StudentDepartment column.

SELECT StudentDepartment,  AVG(StudentAge) as Avg_Age
From Students
GROUP BY StudentDepartment

The results show that the average age of students in the English department is 26.75, while the average ages in the History and Mathematics departments are 25 and 26, respectively.

Table showing the average age by department.

Learn about the different types of SQL tables in this other guide we prepared.

Filtering Aggregate Function Results With HAVING

Finally, you can also filter records grouped by aggregate functions. To do so, you can use the HAVING statement.

For instance, if you want to select average ages for all departments having average ages greater than 25, you can use the AVG() aggregate function in conjunction with the GROUP BY and HAVING clause as follows. 

SELECT StudentDepartment,  AVG(StudentAge) as Avg_Age
From Students
GROUP BY StudentDepartment
Having AVG(StudentAge) > 25

Table showing average ages by department above 25 years of age.

Conclusion

Hopefully these examples of how to use SQL aggregate functions will help you understand them better and use them in practical circumstances. You now know how to calculate the average, count records or use different functions simultaneously. Save our guide for future reference, as these functions are some of the most used. If you wish to get some practice using this function and learn more about SQL, you can try these SQL learning apps suggested by us.

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.

Related Articles