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.
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()
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.
The following SQL statement fetches the number of products in the table.
This will produce the following result.
The below-given command will display those product ids where the unit price is greater than 4.
This will display the following result.
Let's look at how we can use GROUP BY and HAVING functions with the COUNT function.
Consider the following dataset:
The SQL command given below will list the number of customers in each city.
This will produce the following results:
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:
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:
The SQL command below will list the number of customers in each city, having a sum of points greater than 3000.
This will produce the following result:
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.
This will produce the following result.
Become a Creator of Products that Delight Users
Free Webinar | 29 March, Wednesday | 8 PM ISTRegister Now
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:
The above code will give us the minimum quantity in stock in the products table.
Also Read: How to Aggregate Data Using Group By in SQL?
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.
This will produce the following result.
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.
