Microsoft SQL Server

In Microsoft SQL Server, the GROUP BY clause is used to group rows that have the same values in specific columns. It is often used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to summarize or analyze data by groups. For beginners, learning to use GROUP BY is essential for performing data aggregation and generating summary reports from large datasets. The GROUP BY clause helps you categorize data into groups based on column values and apply aggregate functions to each group.

Below is a detailed explanation of how to use the GROUP BY clause with examples and best practices.

1. Basic Syntax of GROUP BY

The basic syntax for using the GROUP BY clause is as follows:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

• Replace column_name with the column by which you want to group the data.
• Replace aggregate_function(column_name) with an aggregate function such as COUNT(), SUM(), AVG(), etc.
• Replace table_name with the actual table name.

Example:

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

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

2. Using GROUP BY with Multiple Columns

You can group data by more than one column by listing multiple columns in the GROUP BY clause. The result will show combinations of unique values from both columns.

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

This query groups products by both category and brand, showing the number of products for each category-brand combination.

3. Using GROUP BY with Aggregate Functions

The GROUP BY clause is commonly used with aggregate functions to summarize data for each group.

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

This query groups the sales data by CustomerID and calculates the total sales for each customer.

4. Using GROUP BY with HAVING

The HAVING clause is used to filter groups based on aggregate values. Unlike WHERE, which filters rows before grouping, HAVING filters groups after the aggregation is done.

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

This query groups sales by customer and filters out customers whose total sales are less than or equal to 1000.

5. Using GROUP BY with ORDER BY

You can sort the results of a GROUP BY query by adding the ORDER BY clause to the query.

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

This query groups products by category, counts how many products are in each category, and sorts the result in descending order of product count.

6. Best Practices for Using GROUP BY

1. Always Include Non-Aggregate Columns in GROUP BY – When using GROUP BY, ensure that all non-aggregate columns in the SELECT clause are included in the GROUP BY clause. Otherwise, the query will generate an error.

   SELECT Category, SUM(Price) 
   FROM Products 
   GROUP BY Category;

2. Use HAVING for Filtering After Aggregation – Use HAVING to filter groups after the aggregation. This is particularly useful for conditions that involve aggregate functions.

   SELECT Category, SUM(Price) 
   FROM Products 
   GROUP BY Category 
   HAVING SUM(Price) > 1000;

3. Be Mindful of Performance on Large Datasets – Using GROUP BY on large tables can impact performance. Ensure that the columns involved in GROUP BY are indexed to improve query speed.

4. Combine with ORDER BY for Sorted Results – When presenting grouped data, it's common to order the result based on the aggregate values. Always use ORDER BY for a better-organized output.

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

5. Test Queries on Subsets of Data – When working with large datasets, test your GROUP BY queries on smaller subsets of data to ensure the logic is correct and to avoid performance issues in production environments.

By mastering the GROUP BY clause, you will be able to generate insightful summaries, statistics, and reports from your SQL Server data, helping you to analyze data more effectively.

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

SELECT state, COUNT(client_id)
FROM org_client
GROUP BY state;

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

SELECT state, MAX(birth_year)
FROM org_client
GROUP BY state;

SELECT CONVERT(DATE, order_date) AS order_date,
COUNT(order_number) AS order_count
FROM act_order
GROUP BY CONVERT(DATE, order_date);

SELECT b.employee_number,
COUNT(a.order_number) AS order_count
FROM act_order a
INNER JOIN org_employee b ON b.employee_id = a.sales_agent_employee_id
GROUP BY b.employee_number;

SELECT city, COUNT(client_id) AS client_count
FROM org_client
WHERE gender = 'F'
GROUP BY city;

Example 1:

In the following example, we will demonstrate the application of WHERE, GROUP BY, and HAVING clauses. It's important to note that the WHERE clause is used before GROUP BY, and the HAVING clause follows GROUP BY. Also, the HAVING clause cannot be used without preceding it with GROUP BY.

Example 1 - Raw data from client table

GROUP BY query

In this example, we will ascertain the number of married female clients in each city, and then identify cities that have more than one married female client.

SELECT city , count(client_id) FROM org_client WHERE gender= 'F' AND married_flag = 1 GROUP BY city HAVING count(client_id) > 1

Step 1, Apply WHERE conditions

In the image above, the cells highlighted with a green background and white font represent those that satisfy both criteria of the WHERE condition (being married females).

Step 1 output after applying WHERE condition

Step 2 - Apply GROUP BY

In the provided image, we need to group the data by the 'city' column. As observed, Albany is represented by 2 client rows, while the other cities each have only one row.

Step 2 output, after applying GROUP BY

Step 3 - Apply HAVING Clause

Using the HAVING clause essentially means filtering the grouped rows based on the condition specified in the HAVING clause. In this example, we are looking for groups with a client count greater than one. The cells highlighted in green with white font are those that meet the condition set by the HAVING clause.

Final output of entire query