SQL Aggregate Functions: SUM(), COUNT(), AVG(), Functions

An aggregate function in SQL performs a calculation on multiple values and returns a single value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc. An aggregate function ignores NULL values when it performs the calculation, except for the count function. 

In this article, we will discuss the following topics: 

  • What is an aggregate function in SQL?
  • COUNT() function
  • SUM() function
  • AVG() function
  • MIN() function
  • MAX() function

What is an Aggregate Function in SQL?

An aggregate function in SQL returns one value after calculating multiple values of a column. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

Various types of SQL aggregate functions are:

  • Count()
  • Sum()
  • Avg()
  • Min()
  • Max()

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

COUNT() Function

The COUNT() function returns the number of rows in a database table.

Syntax:

COUNT(*)  

or  

COUNT( [ALL|DISTINCT] expression )  

Example:

We will use the ‘products’ table from the sample database for our demonstration.

dataset-sql-aggregate-function

The following SQL statement fetches the number of products in the table.

count-ex-1

This will produce the following result.

output-count-ex-1

The below-given command will display those product ids where the unit price is greater than 4.

count-ex-2

This will display the following result.

output-count-ex-2

Let's look at how we can use GROUP BY and HAVING functions with the COUNT function.

Consider the following dataset:

group-dataset

The SQL command given below will list the number of customers in each city.

count-groupby

This will produce the following results:

output-count-groupby

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax:

SUM()  

or  

SUM( [ALL|DISTINCT] expression )  

Example:

The following SQL statement finds the sum of the "unit price" fields in the "products" table:

sum-sql-aggregate-function


This will produce the following result.

Let’s look at how we can use GROUP BY and HAVING functions with the SUM function.

Consider the following dataset:

group-dataset

The SQL command below will list the number of customers in each city, having a sum of points greater than 3000.

sum-groupby

This will produce the following result:

output-sum-groupby

AVG() Function

The AVG() function calculates the average of a set of values.

Syntax:

AVG()  

or  

AVG( [ALL|DISTINCT] expression )  

Example:

The following SQL command calculates the average quantity in stock.

avg-aggregate-functions

This will produce the following result.

output-avg-1

MIN() Function

The MIN() aggregate function returns the lowest value (minimum) in a set of non-NULL values.

Syntax:

MIN()  

or  

MIN( [ALL|DISTINCT] expression )  

Example:

min-sql-aggregate-function

The above code will give us the minimum quantity in stock in the products table.

output-min

MAX() Function

The MAX() aggregate function returns the highest value (maximum) in a set of non-NULL values.

Syntax:

AVG()  

or  

AVG( [ALL|DISTINCT] expression )  

Example:

The code depicted below will give us the maximum quantity in stock in the products table.

max-aggregate-functions

This will produce the following result.

output-max-1

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

The aggregate function in SQL is very powerful in the database. It serves the same purpose as their equivalents in MS Excel. In this article, we have seen several examples of aggregate functions.

If you wish to learn more about SQL, then check out our SQL certification course.

This SQL certification course helps you get the basics right with the practical training you need to work with SQL databases and use them in the applications you use or create. On completing the course, you will have a work-ready understanding of how to structure your database correctly, create error-free and efficient SQL statements and clauses, and design and manage your SQL databases for scalable growth.

If you have any questions, please feel free to ask them in our comments section, and our experts will promptly answer them for you. 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.