Microsoft SQL Server

In Microsoft SQL Server, aggregate functions such as COUNT, SUM, AVG, MIN, and MAX are essential tools for performing calculations on sets of data. These functions are used to summarize data, allowing you to count records, calculate totals, find averages, and identify minimum and maximum values within a column. For beginners, mastering these functions is key to working with large datasets and generating meaningful reports.

Below is a detailed explanation of each aggregate function with examples and best practices.

1. COUNT Function

The COUNT function returns the number of rows that match a specified condition or the number of non-NULL values in a column.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

SELECT COUNT(*) AS TotalProducts 
FROM Products;

This query returns the total number of products in the Products table.

2. SUM Function

The SUM function returns the total sum of numeric values in a column. It’s typically used to calculate totals for numerical data such as sales amounts or quantities.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

SELECT SUM(SaleAmount) AS TotalSales 
FROM Sales;

This query returns the total sales amount from the Sales table.

3. AVG Function

The AVG function calculates the average value of a numeric column. It’s useful for finding the mean of values such as prices or sales figures.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

SELECT AVG(Price) AS AveragePrice 
FROM Products;

This query calculates the average price of all products in the Products table.

4. MIN Function

The MIN function returns the smallest (minimum) value in a column. It is typically used to find the lowest price, the earliest date, or the smallest value in a set of data.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Example:

SELECT MIN(Price) AS LowestPrice 
FROM Products;

This query retrieves the lowest product price from the Products table.

5. MAX Function

The MAX function returns the largest (maximum) value in a column. It’s useful for identifying the highest value in a dataset, such as the highest price or the most recent date.

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example:

SELECT MAX(SaleAmount) AS HighestSale 
FROM Sales;

This query retrieves the highest sale amount from the Sales table.

6. Best Practices for Using Aggregate Functions

1. Use Aggregate Functions with GROUP BY – Aggregate functions like SUM, AVG, COUNT, MIN, and MAX are often used in combination with the GROUP BY clause to group data and apply calculations to each group.

   SELECT Category, COUNT(*) AS ProductCount 
   FROM Products 
   GROUP BY Category;

   This query groups the products by category and counts how many products are in each category.

2. Filter Data with WHERE Before Aggregation – Use the WHERE clause to filter rows before applying aggregate functions to ensure that you’re working with the correct subset of data.

   SELECT SUM(SaleAmount) 
   FROM Sales 
   WHERE SaleDate > '2023-01-01';

   This query calculates the total sales for transactions that occurred after January 1, 2023.

3. Use HAVING to Filter After Aggregation – If you want to filter results based on aggregate values (such as filtering groups with a SUM greater than a specific value), use the HAVING clause.

   SELECT CustomerID, SUM(SaleAmount) AS TotalSales 
   FROM Sales 
   GROUP BY CustomerID 
   HAVING SUM(SaleAmount) > 1000;

   This query groups sales by customer and filters for customers whose total sales exceed 1000.

4. Be Mindful of NULL Values – By default, aggregate functions ignore NULL values in a column. If you need to count NULL values, use COUNT(*) or handle NULL cases explicitly with ISNULL() or COALESCE().

   SELECT COUNT(*) AS TotalOrders 
   FROM Orders 
   WHERE OrderDate IS NULL;

5. Use Aggregate Functions for Summarizing Large Datasets – Aggregate functions are powerful tools for summarizing large datasets, making them ideal for generating summary reports, dashboards, or analytics.

By mastering these aggregate functions, you can efficiently analyze and summarize data in SQL Server, allowing you to derive valuable insights from your datasets.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT COUNT(*)
FROM org_employee;

SELECT COUNT(DISTINCT department)
FROM org_employee;

SELECT SUM(salary)
FROM org_employee;

SELECT AVG(salary)
FROM org_employee;

SELECT MIN(salary)
FROM org_employee;

SELECT MAX(salary)
FROM org_employee;

SELECT a.*
FROM org_employee a
INNER JOIN (SELECT MIN(salary) AS min_salary FROM org_employee) b
ON a.salary = b.min_salary;

SELECT department,
SUM(salary) AS total_salary
FROM org_employee
GROUP BY department
ORDER BY total_salary DESC;

SELECT department,
COUNT(*) AS employee_count
FROM org_employee
GROUP BY department;

SELECT department,
MAX(salary) AS highest_salary
FROM org_employee
GROUP BY department;

SELECT department,
MIN(salary) AS lowest_salary
FROM org_employee
GROUP BY department;

Example 1:

Here are few SQL examples showcasing the utilization of COUNT, SUM, and MAX SQL functions.

Example 1 - Raw data from employee table

Example 1 - COUNT

SELECT COUNT(*) FROM org_employee;

Example 1 - Query data mapping

Essentially, you are required to determine the count of rows in the table.

Example 1 - Query Output

Example 2 - COUNT DISTINCT

SELECT COUNT(DISTINCT department) FROM org_employee;

Example 2 - Query data mapping

The green-colored boxes that have been highlighted represent unique department names that will be considered for the count.

Example 2 - Query Output

Example 3 - SUM by categroy using GROUP BY

SELECT department ,SUM(salary) FROM org_employee GROUP BY department ORDER BY SUM(salary) DESC;

Example 3 - Query data mapping

The salaries from each department are aggregated by department, summing them together.

Example 3 - Query Output

Example 4 - MAX by categroy using GROUP BY

SELECT department ,MAX(salary) FROM org_employee GROUP BY department;

Example 4 - Query data mapping

Green-colored boxes represent the highest salary from each corresponding department.

Example 4 - Query Output