SQL Aggregate Functions

Read this article to understand the SQL Aggregate Functions along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of Aggregate Functions with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of Aggregate Functions and why we should use it.

Definition

We can do our initial analysis like count the number of records, finding smallest and largest value, calculating the sum of all values etc. using aggregate functions. There are lots of aggregate functions that supports to different database engines.

We will learn below aggregate functions that are mostly used for simpler calculations. Aggregate functions perform the operation on set of data and return single value. The output of aggregate function is always deterministic. Deterministic it means when we execute the operation on similar data then the output will always same. Few important aggregate functions

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Reasons to Use

Aggregate functions are commonly used in SQL to perform calculations on sets of data and return a single result.

1. Aggregate functions allow you to summarize data by performing calculations on groups of rows or an entire table.
2. Aggregate functions can be used to combine data from multiple rows into a single value.
3. Aggregate functions are useful for calculating various statistics, such as minimum (MIN) and maximum (MAX) values, average (AVG), median, mode, and standard deviation (STDDEV), among others.
4. Aggregate functions can be used to perform data validation checks on your data.

Count

This function returns the number of values or returns the number of records in the selected data. We can apply the criteria and count the number of records using count function. Count function counts the null values also. Basically it counts the number of records inspite of values.

Syntax

SELECT COUNT([ALL|DISTINCT] * | column_name) 
FROM table_name
[WHERE condition];

Example

Example 1 :

Lets count the number of records present into the customers table.

When to use the ALL or DISTINCT?

Lets assume you want to count the distinct values of age column. In above dataset we have 4 distinct values. Lets validate it using distinct keyword.

Example 2 :

AGE_COUNT
4

The output of ALL and * will be the same in all cases.

Example 3 :

COUNT
2

This example count the number of records after applying the conditions. It counts the total records of USA country.

Great!! If we have to analyse the data based on count then we can use count along with the where condition if required to count the records. Lets do some addition of the values using next aggregate function.

Sum

This function returns the sum of all values excepting null values. Sum function return single value which is also deterministic. This function combines the result of all selected data from the select clause. This functions operate on numeric columns.

Syntax

SELECT SUM([ALL|DISTINCT] column_name) 
FROM table_name
[WHERE condition];

Lets do the sum of all age values which are present into table.

Example

Example 4 :

Above example returns the addition of all age values. Lets see the below example for distinct value addition.

Example 5 :

Great!! We learned about the COUNT function which count the records while SUM function perform the addition of all values. Lets learn about the AVG function.

Avg

The AVG function returns the average value of all values. AVG function skip the null values from calculations.

Syntax

SELECT AVG([ALL|DISTINCT] column_name) 
FROM table_name
[WHERE condition];

Lets see the average value of age.

Example

Example 6 :

Example 7 :

AVG function skips the null values. What it means?

If we are performing average operation on the table and table having some null records into the respective columns. The average will be perform on the records which has the values and all records are skipped which doesn’t have values.

Correct! AVG function gives the wrong result if we have to perform the actual average operation on all data even we have null values and we are expecting the null means 0.

Min & Max

The MIN and MAX function are used to identify the minimum and maximum value from the selected dataset. This functions return only single value and which is also deterministic.

Syntax

SELECT MIN(column_name) 
FROM table_name
[WHERE condition];

SELECT MAX(column_name) 
FROM table_name
[WHERE condition];

Select the minimum and maximum age from the customers table.

Example

Example 8 :

I observed that, We have used MIN and MAX function together. Yes, We can use all aggregate function together wherever required.

Please understand below SQL and it’s output.

Example 9 :

Great!! Now we are able to do some analysis on the data using aggregate functions.

Linking

Aggregate functions are used in Select statement. We can use Where clause along with aggregate functions. You can refer all the interview questions. Please refer all the links for understanding the Aggregate functions in depth.

SQL Aggregate Functions with Latest SQL Interview Questions 2023

Basic Aggregate Functions SQL Interview questions

1. What are some commonly used aggregate functions in SQL?
2. How do you use aggregate functions in a SQL query?
3. Can you use aggregate functions with other SQL clauses?

Intermediate Aggregate Functions SQL Interview questions

1. Can you use aggregate functions on NULL values in SQL?
2. Can you use aggregate functions in a subquery in SQL?

Advanced Aggregate Functions SQL Interview questions

1. What is the order of execution for aggregate functions and other clauses in a SQL query?
2. How do you handle NULL values with aggregate functions in SQL?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : What is the order of execution for aggregate functions and other clauses in a SQL query?

Ans : In general, the order of execution in a SQL query is as follows:

1. FROM: Specifies the source table or tables for the query.
2. WHERE: Filters the rows before grouping and aggregation.
3. GROUP BY: Groups the rows based on one or more columns.
4. HAVING: Filters the groups based on aggregate function results.
5. SELECT: Performs calculations using aggregate functions and retrieves the final result set.
6. ORDER BY: Sorts the query results.
7. LIMIT/OFFSET (if applicable): Applies row limiting or pagination.

Quiz

Click on this link to solve few MCQ questions on Aggregate Functions clause.

If we see the data of country, we have 3 countries. Can we do the analysis on these countries like what is the minimum age of people in USA, UK etc?

ohh… Great question. Lets think about the options and comment in to the comment section.

Now guess what we will cover in to the next session.

I hope you enjoyed this session. Happy offline learning… Lets meet in next session.