Aggregate functions in Microsoft SQL Server are used to perform a calculation on a set of values and return a single value. These functions operate on a group of rows and are often used in conjunction with the GROUP BY clause. Here are some commonly used aggregate functions:
1. COUNT:
Counts the number of rows in a set.
Syntax:
--To count all rows
SELECT COUNT(*) AS TotalRows
FROM TableName;
--To count distinct rows
SELECT COUNT(DISTINCT columnName) AS DistinctCount
FROM YourTableName;
--To count based on condition
SELECT COUNT(*) AS ConditionCount
FROM YourTableName
WHERE YourCondition;
Example:
Let us create some sample data to test the functions.
CREATE DATABASE PRACTICE_DB;
USE PRACTICE_DB
GO
CREATE TABLE PRODUCTS
(
PRODUCT_ID INT
, PRODUCT_NAME CHAR(255)
, PRODUCT_CATEGORY CHAR(255)
, PRODUCT_PRICE FLOAT
, CURRENCY CHAR(5)
);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (1, 'Soap', 'Beauty Products', 25, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (2, 'TV', 'Electronics', 500, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (3, 'Laptop', 'Electronics', 900, 'USD');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (4, 'Hand cream', 'Beauty Products', 10, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (5, 'Barbie', 'Toys', 56, 'GBP');
Below are few examples of COUNT function.
--To count all rows
SELECT COUNT(*) AS TotalRows
FROM PRODUCTS;
--To count distinct rows
SELECT COUNT(DISTINCT PRODUCT_CATEGORY) AS DistinctCount
FROM PRODUCTS;
--To count based on condition
SELECT COUNT(*) AS ConditionCount
FROM PRODUCTS
WHERE PRODUCT_CATEGORY = 'Electronics';
--Count with GROUP_BY
SELECT PRODUCT_CATEGORY, COUNT(*) AS CategoryCount
FROM PRODUCTS
GROUP BY PRODUCT_CATEGORY;
--Count with HAVING
SELECT PRODUCT_CATEGORY, COUNT(*) AS CategoryCount
FROM PRODUCTS
GROUP BY PRODUCT_CATEGORY
HAVING COUNT(*) > 1;Output:
Executing all the above 5 statements will result as follows.
2. SUM:
Calculates the sum of a numeric column.
Syntax:
SELECT SUM(column_name) AS sum_result
FROM table_name;
Example:
--Sum of all product prices
SELECT SUM(PRODUCT_PRICE) AS TotalPrice
FROM PRODUCTS;
Output:
3. AVG:
Calculates the average of a numeric column.
Syntax:
SELECT AVG(column_name) AS avg_result
FROM table_name;Example:
--Avg of all product prices
SELECT AVG(PRODUCT_PRICE) AS AvgPrice
FROM PRODUCTS;
Output:
4. MIN:
Returns the minimum value in a column.
Syntax:
SELECT MIN(columnName) AS MinResult
FROM tablename;Example:
--Min of product prices
SELECT MIN(PRODUCT_PRICE) AS MinPrice
FROM PRODUCTS;Output:
5. MAX:
Returns the maximum value in a column.
Syntax:
SELECT MAX(columnName) AS MaxResult
FROM tablename;Example:
--Max of product prices
SELECT MAX(PRODUCT_PRICE) AS MaxPrice
FROM PRODUCTS;Output:
6. GROUP BY:
Groups rows that have the same values in specified columns into aggregated data.
Syntax:
SELECT column1, [column2], [..column n], aggregate_function
FROM table_name
GROUP BY column1, column2, [..column n];
Example:
--Sum of product prices grouped by product category
SELECT PRODUCT_CATEGORY, SUM(PRODUCT_PRICE) AS TotalPrice
FROM PRODUCTS
GROUP BY PRODUCT_CATEGORY;
--Avg of product prices grouped by product category
SELECT PRODUCT_CATEGORY, AVG(PRODUCT_PRICE) AS AvgPrice
FROM PRODUCTS
GROUP BY PRODUCT_CATEGORY;Output:
7. HAVING:
Filters the results of a GROUP BY based on a condition.
Syntax:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2
HAVING condition;Example:
--Sum of Product prices having total price > 200
SELECT PRODUCT_CATEGORY, SUM(PRODUCT_PRICE) AS TotalPrice
FROM PRODUCTS
GROUP BY PRODUCT_CATEGORY
HAVING SUM(PRODUCT_PRICE) > 200;Output:
If you want to learn more about GROUP BY and HAVING clause, you can check the Microsoft documentation or here.
8. GROUPING SETS:
Grouping Sets is a feature in SQL that allows you to specify multiple grouping sets within a single GROUP BY clause. This allows you to aggregate data at different levels in a single query, producing results for various combinations of grouped columns.
You can use GROUPING SETS to generate a result set that includes aggregated values at different levels of granularity in a single query, avoiding the need for multiple queries or subqueries.
Syntax:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS ( (column1, column2), (column1), () );
Example:
SELECT PRODUCT_CATEGORY, CURRENCY, SUM(PRODUCT_PRICE) AS ProductPrice
FROM PRODUCTS
GROUP BY GROUPING SETS (PRODUCT_CATEGORY, CURRENCY, (PRODUCT_CATEGORY, CURRENCY), ());We can group the results by PRODUCT_CATEGORY, CURRENCY and (PRODUCT_CATEGORY, CURRENCY).
The empty set () is for grand total.
Output:
9. ROLLUP:
ROLLUP is another grouping-related feature in SQL that, like GROUPING SETS, allows you to produce subtotals and grand totals in a single query.
Syntax:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY ROLLUP (column1, column2);
Example:
SELECT PRODUCT_CATEGORY, CURRENCY, SUM(PRODUCT_PRICE) AS ProductPrice
FROM PRODUCTS
GROUP BY ROLLUP (PRODUCT_CATEGORY, CURRENCY);Output:
10. CUBE:
In Microsoft SQL Server (MS SQL), the CUBE operator is used in conjunction with the GROUP BY clause to generate cross-tabulated results. Like ROLLUP and GROUPING SETS, CUBE allows you to produce subtotals and grand totals in a single query, but it goes a step further by generating all possible combinations of the grouped columns.
Syntax:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY CUBE (column1, column2);
Example:
SELECT PRODUCT_CATEGORY, CURRENCY, SUM(PRODUCT_PRICE) AS ProductPrice
FROM PRODUCTS
GROUP BY CUBE (PRODUCT_CATEGORY, CURRENCY);These functions are powerful tools for summarizing and analyzing data in SQL Server, especially when dealing with large datasets or when grouping data by certain criteria.
Output:
From the above example, we can see the totals by PRODUCT_CATEGORY and CURRENCY (highlighted in green), PRODUCT_CATEGORY alone (highlighted in orange), CURRENY along (highlighted in yellow) and Grand Total (not highlighted row).
