How to Use GROUP BY and WHERE Condition in SQL

In this post, I’ll explain how to use GROUP BY and WHERE condition in SQL, provide examples, and show you the syntax step by step.

When working with SQL, grouping data is a common task that helps summarize information for reporting or analysis. The GROUP BY statement is a powerful tool that allows you to group rows based on specific columns, enabling aggregate calculations like totals, averages, and counts. Pairing the GROUP BY statement with the WHERE condition lets you filter data before grouping it, giving you even more control over your results.

What is the GROUP BY Statement?

The GROUP BY statement is used to group rows that have the same values in specified columns. Once grouped, you can apply aggregate functions like SUM, COUNT, or AVG to the groups, rather than individual rows.

For example, you might want to calculate total sales per region or count the number of orders per customer. The GROUP BY clause is what makes this possible.

Syntax for GROUP BY with WHERE

To understand how to use GROUP BY and WHERE in SQL, let’s first break down the syntax:

SELECT column_name, aggregate_function(column_name)  
FROM table_name  
WHERE condition  
GROUP BY column_name

1. SELECT: Specifies the columns you want to retrieve and the aggregate function.
2. WHERE: Filters the rows before grouping them.
3. GROUP BY: Groups the remaining rows by one or more columns.

Example 1: Basic GROUP BY and WHERE

Let’s say you have a OrderDetails table with the following columns: OrderID, ProductID, and UnitPrice and Quantity. If you want to find how many low-priced items (with unit price lower than $10) did you sell per each order, you can write the following SQL query:

SELECT [OrderID]
      ,SUM([Quantity]) AS [OrderQuantity]
FROM [Order Details]
WHERE [UnitPrice] < 10
GROUP BY [OrderID]

What happens in the code above?

• The WHERE clause filters out rows where Unit Price is less than $10.
• The GROUP BY statement groups the remaining rows by OrderID.
• The SUM function calculates the total Order Quantity for each OrderID.

Here are the results in SSMS:

Example 2: Using Multiple Columns with GROUP BY

You can group by more than one column. Let’s use the Products table from our Northwind database. For example, if you want to calculate total units in stock by both SupplierID and CategoryID, but only for items with prices lower than $20, you can use the following query:

SELECT [SupplierID]
      ,[CategoryID]
      ,SUM([UnitsInStock]) AS [TotalStock]
FROM [Products]
WHERE [UnitPrice] < 20
GROUP BY [SupplierID], [CategoryID]

Here, the rows are grouped first by SupplierID and then by CategoryID.

Here is how it looks in SQL Server Management Studio:

You can notice how these two lines have been grouped:

Example 3: How to Use GROUP BY and WHERE Together in SQL

Using both the WHERE condition and GROUP BY together gives you the flexibility to filter data first and then summarize it. For example, to count the number of products sold from each category but only include products that have Unit Price above $30:

SELECT 
      [CategoryID]
      ,COUNT(ProductID) AS [ProductCount]
FROM [Products]
WHERE [UnitPrice] > 30
GROUP BY [CategoryID]

Here is what our code looks like in SSMS:

The key point to remember: The WHERE clause always filters data before grouping.

Common Mistake: Mixing GROUP BY and HAVING

While the WHERE clause filters rows before grouping, the HAVING clause is used to filter groups after grouping.

For example, to include only categories with total stock above 500 units:

SELECT 
      [CategoryID]
      ,SUM([UnitsInStock]) AS [TotalStock]
FROM [Products]
GROUP BY [CategoryID]
HAVING SUM([UnitsInStock]) > 500

Here are the results in SSMS:

This is an important distinction when learning how to use GROUP BY and WHERE together in SQL.

When to Use GROUP BY and WHERE

The combination of GROUP BY and WHERE is particularly useful in scenarios like:

• Summarizing sales data for a specific time period.
• Filtering out irrelevant data (e.g., low sales) before grouping.
• Counting distinct items that meet certain criteria.

Conclusion

Understanding how to use GROUP BY and WHERE condition in SQL is essential for effective data analysis. By filtering rows first with WHERE and then grouping them with GROUP BY, you can create powerful queries to summarize data.

Practice these examples to get comfortable with the syntax, and experiment with your own datasets. Mastering how to use GROUP BY and WHERE together in SQL will make your queries more efficient and insightful!

Also, make sure you check my last post on Aggregate Functions in SQL with Syntax and Examples.

If you would like to learn more about SQL, check out this amazing book on Amazon.