SQL Aggregate Functions: SUM, AVG, MIN, MAX and COUNT

SQL Server Aggregate Functions

An aggregate function in SQL Server is a function that performs a calculation on a set of values and returns a single value. It is used to summarize or group data from one or more columns in a table. 
SQL Server provides several built-in aggregate functions that can be used to calculate the sum, average, minimum, maximum, and count of a set of values. 

Commonly used Aggregate functions: 

1. SUM(): calculates the total sum of a set of values in a column. 

2. AVG(): calculates the average of a set of values in a column. 

3. MIN(): returns the minimum value from a set of values in a column. 

4. MAX(): returns the maximum value from a set of values in a column. 

5. COUNT(): counts the number of values in a column. 

Aggregate functions can be used in combination with the GROUP BY clause to group data by one or more columns and perform calculations on each group. The result of an aggregate function is a single value that represents the summary of the values in the specified column(s). 

To gain a better understanding of aggregate functions and how they can be used, we will perform some examples. 

Let’s create a table called “Orders” with columns for “OrderID”, “CustomerID”, “OrderDate”, “Product”, and “Price”. We will then insert some sample data into the table: 

CREATE TABLE Orders (
                OrderID INT PRIMARY KEY,
                CustomerID INT,
                OrderDate DATE,
                Product VARCHAR(50),
                Price FLOAT
);

INSERT INTO Orders VALUES (1, 1001, ‘2022-03-05’, ‘Shirt’, 25.99);
INSERT INTO Orders VALUES (2, 1002, ‘2022-03-10’, ‘Pants’, 39.99);
INSERT INTO Orders VALUES (3, 1003, ‘2022-03-12’, ‘Shoes’, 79.99);
INSERT INTO Orders VALUES (4, 1001, ‘2022-03-15’, ‘Jacket’, 99.99);
INSERT INTO Orders VALUES (5, 1002, ‘2022-03-18’, ‘Socks’, 4.99);

Now, we have our “Orders” table with sample data, we can use the following aggregate functions to perform calculations: 
 

• SUM() 
  The SUM() function calculates the total sum of a set of values in a column. 
  
  SELECT SUM(Price) AS TotalSales
  FROM dbo.Orders;
  
  Result: This query returns the total sum of all prices in the “Orders” table as “TotalSales”, you can see in the below image. 

• AVG() 
  The AVG () function calculates the average of a set of values in a column. 
  
  SELECT AVG(Price) AS AveragePrice
  FROM dbo.Orders;
  
  Result: This query returns the average price of all orders in the “Orders” table as “AveragePrice”, as you can see in the below image. 

• MIN() 
  The MIN() function returns the minimum value from a set of values in a column. Use following syntax for fetch minimum value from a column. 
  
  SELECT MIN(Price) AS MinPrice
  FROM dbo.Orders;
  
  Result: This query returns the minimum price from the “Orders” table as “MinPrice”. 

• MAX():
  The MAX() function returns the maximum value from a set of values in a column. Use following syntax for fetch maximum value from a column. 
  
  SELECT MAX (Price) AS MaxPrice
  FROM dbo.Orders;
  
  Result: This query returns the maximum price from the “Orders” table as “MaxPrice”. As you can see the below image. 

• COUNT():
  The COUNT() function counts the number of values in a column. 
  
  SELECT COUNT(OrderID)AS NumOrders
  FROM dbo.Orders;

Result: This query returns the number of orders in the “Orders” table as “NumOrders”. 

Result: 

Complex Examples: 

First, we will create one more table to understand some complex examples.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Salesperson VARCHAR(50),
    SaleDate DATE,
    Product VARCHAR(50),
    UnitsSold INT,
    PricePerUnit FLOAT
);

INSERT INTO Sales VALUES (1, ‘John’, ‘2022-03-05’, ‘Shirt’, 5, 25.99);
INSERT INTO Sales VALUES (2, ‘Jane’, ‘2022-03-10’, ‘Pants’, 8, 39.99);
INSERT INTO Sales VALUES (3, ‘Joe’, ‘2022-03-12’, ‘Shoes’, 3, 79.99);
INSERT INTO Sales VALUES (4, ‘John’, ‘2022-03-15’, ‘Jacket’, 2, 99.99);
INSERT INTO Sales VALUES (5, ‘Jane’, ‘2022-03-18’, ‘Socks’, 10, 4.99);

Now, let’s see some examples: 

• GROUP BY and COUNT() 

The GROUP BY clause is used to group rows that have the same values into summary rows. We can use the COUNT() function to count the number of rows in each group. 

Example: Write a query to retrieve the number of sales made by each salespersons in the “Sales” table. 

SELECT Salesperson, COUNT(*) AS NumSales
FROM dbo.Sales
GROUP BY Salesperson;

• GROUP BY and SUM() 

We can also use the GROUP BY clause with the SUM() function to calculate the total sales made by each salesperson. 

Example: Write a query that returns the total sales made by each salesperson in the “Sales” table. 

SELECT Salesperson, SUM(UnitsSold * PricePerUnit) AS ToatlSales
FROM dbo.Sales
GROUP BY Salesperson;

• HAVING 

The HAVING clause is used to filter groups based on a specified condition. For example, we can use the HAVING clause with the SUM() function to filter out salespeople who have made less than a certain amount of sales. 

Example: Write a query that returns the salespeople who have made total sales greater than $500. 

SELECT Salesperson, SUM(UnitsSold * PricePerUnit) AS TotalSales
FROM dbo.Sales
GROUP BY Salesperson
HAVING SUM(UnitsSold * PricePerUnit) > 500;

• DISTINCT 

The DISTINCT keyword is used to eliminate duplicate values in a column. We can use it with the COUNT () function to count the number of unique products sold. 

Example: Write a query that returns the number of unique products sold in the “Sales” table. 

SELECT COUNT(DISTINCT Product) AS NumProductsSold
FROM dbo.Sales;

Here are some more aggregate functions in SQL Server: 

• COUNT(DISTINCT) 

The COUNT(DISTINCT) function is used to count the number of unique values in a column. For example, we can use it to count the number of unique customers who have placed orders. 

• STDEV() 

The STDEV() function is used to calculate the standard deviation of a set of values in a column. For example, we can use it to calculate the standard deviation of sales data to measure the variability of sales. 

• VARP() 

The VARP() function is used to calculate the variance of a set of values in a column. For example, we can use it to calculate the variance of sales data to measure the variability of sales. 

• CHECKSUM_AGG() 

The CHECKSUM_AGG() function is used to generate a checksum value for a set of values in a column. For example, we can use it to generate a checksum value for customer data to detect changes in customer information. 

• GROUPING() 

The GROUPING() function is used to determine whether a column value is a NULL or a grouping value in a GROUP BY operation. For example, we can use it to determine whether a sales value is a sub-total or a grand total in a sales report. 
These functions can be used to perform advanced calculations and statistical analysis on large datasets in SQL Server. 

Advantages of using aggregate functions in SQL Server: 

• They provide a quick and efficient way to perform calculations on large datasets. 
• They can be used to summarize and analyse data in a variety of ways, including calculating sums, averages, counts, and more. 
• They can be used in conjunction with other SQL statements, such as GROUP BY and ORDER BY, to organize and analyse data in different ways. 

• They can be used to create complex queries that perform advanced calculations and statistical analysis on large datasets. 
• They can help identify patterns and trends in data, which can be useful for making business decisions. 

Overall, aggregate functions are a powerful tool for working with data in SQL Server and can provide valuable insights into the information stored in a database. 

 

limitations and restrictions of aggregate functions in SQL Server: 

• Aggregate functions cannot be used in the WHERE clause of a SQL statement. 
• Aggregate functions cannot be nested within each other. For example, we cannot use the SUM() function within the AVG() function. 
• Aggregate functions only operate on a single column of data at a time. If we need to perform calculations on multiple columns, we will need to use multiple aggregate functions or perform joins on multiple tables. 
• Aggregate functions cannot be used with the DISTINCT keyword. For example, we cannot use COUNT(DISTINCT SUM(sales)) to count the number of distinct sales amounts. 
• Aggregate functions may return unexpected results when used with NULL values. For example, the AVG() function may return NULL if any of the values in the column are NULL. 
• The result of an aggregate function may be truncated if the data type of the result is too large to fit into the designated space. For example, if we use the AVG() function on a column of type FLOAT, the result may be truncated if the precision of the result is too large to fit into the FLOAT data type. 
• Aggregate functions may be affected by the collation settings of the database. For example, the ORDER BY clause used with an aggregate function may produce different results depending on the collation settings. 

Frequently asked questions for interviews:

Here are some interview questions related to aggregate functions in SQL Server: 

1. What is an aggregate function in SQL Server? 

2. What are the most commonly used aggregate functions in SQL Server? 

3. Can aggregate functions be used in the WHERE clause of a SQL statement? Why or why not? 

4. Can aggregate functions be nested within each other in SQL Server? Why or why not? 

5. What happens when an aggregate function is used with NULL values in SQL Server? 

6. How can you avoid unexpected results when using aggregate functions with NULL values in SQL Server? 

7. Can you use the DISTINCT keyword with aggregate functions in SQL Server? Why or why not? 

8. What are some limitations and restrictions of aggregate functions in SQL Server? 

9. How can you use aggregate functions to calculate the average salary of employees in a table? 

10. How can you use aggregate functions to count the number of unique cities in a table? 

11. Can you use aggregate functions with text or date/time data types in SQL Server? Why or why not? 

12. How do you alias the result of an aggregate function in SQL Server? 

13. Can you use aggregate functions with the GROUP BY clause in SQL Server? Why or why not? 

14. What is the difference between the SUM() function and the COUNT() function in SQL Server? 

15. Can you use the AVG() function with a column of type VARCHAR in SQL Server? Why or why not? 

These questions can help assess a candidate’s understanding of aggregate functions in SQL Server and their ability to apply them to real-world scenarios. 

Conclusion: 

SQL Server provides a wide range of aggregate functions, including the most commonly used ones such as SUM, AVG, COUNT, MAX, and MIN, as well as more advanced functions like STDEV, VAR. These functions can be used in combination with other SQL clauses such as GROUP BY, HAVING, and ORDER BY to create complex queries that extract and summarize specific data subsets. 

Overall, aggregate functions in SQL Server are a powerful tool for data analysis and manipulation, enabling users to extract insights from large and complex datasets efficiently.