Primary Grouping Functions
- COUNT(): Counts the number of rows in each group.
SELECT column1, COUNT(*) as row_count
FROM table_name
GROUP BY column1;
- SUM(): Calculates the sum of values in a column for each group.
SELECT column1, SUM(column2) as total_sum
FROM table_name
GROUP BY column1;
- AVG(): Computes the average of values in a column for each group.
SELECT column1, AVG(column2) as average_value
FROM table_name
GROUP BY column1;
- MAX(): Retrieves the maximum value in a column for each group.
SELECT column1, MAX(column2) as max_value
FROM table_name
GROUP BY column1;
- MIN(): Retrieves the minimum value in a column for each group.
SELECT column1, MIN(column2) as min_value
FROM table_name
GROUP BY column1;
SQL Server Group Functions
The group function in SQL Server provides a powerful tool for performing calculations on groups of rows, allowing you to group data based on specific criteria. This function is important when you want to analyze and summarize information from multiple records in a data structure. The basic group functions are COUNT, SUM, AVG, MAX, and MIN.
Syntax:
The basic syntax for using the grouping function involves a GROUP BY clause with the custom aggregation function. Here’s the whole process:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
- column1: The group of columns in which you want to pass the data.
- aggregate_function(column2): The group function (e.g., COUNT, SUM, AVG, MAX, MIN) applied to the new columns of each group.
- table_name: The name of the table containing the data.