How to use GROUP BY in SQL

By Tan Lee Published on Mar 07, 2025 112

You can use the GROUP BY clause in SQL to organize rows based on one or more columns. This creates a single row per group in the result, where each group contains the columns used for grouping and the aggregated results of functions like COUNT, MIN, MAX, AVG, or SUM.

Let’s say you have the following table tracking sales transactions and you want to know how many products each store sold:

Sales Table:

StoreName	ProductName	Quantity
Store A	Product 1	10
Store B	Product 2	5
Store A	Product 3	7
Store B	Product 1	8
Store A	Product 2	12

To calculate the total number of products each store sold, you can use the GROUP BY clause as follows:

        
              SELECT StoreName, SUM(Quantity) as TotalSold
FROM Sales
GROUP BY StoreName

This will return the total quantity of products sold by each store:

StoreName	TotalSold
Store A	29
Store B	13

GROUP BY Multiple Columns

You can also group by multiple columns.

For example, if you want to know how many of each product was sold by each store, you can group by both StoreName and ProductName:

        
              SELECT StoreName, ProductName, SUM(Quantity) as TotalSold
FROM Sales
GROUP BY StoreName, ProductName

This produces the following result:

StoreName	ProductName	TotalSold
Store A	Product 1	10
Store A	Product 2	12
Store A	Product 3	7
Store B	Product 1	8
Store B	Product 2	5

GROUP BY and NULLs

When a grouping column contains a NULL value, it creates a separate group for those rows.

For example, if some rows have missing store names, they will be grouped together as NULL.

To avoid this, you can filter out rows with NULL values in the grouping column using the WHERE clause:

        
              SELECT StoreName, SUM(Quantity) as TotalSold
FROM Sales
WHERE StoreName IS NOT NULL
GROUP BY StoreName

Error: Column is Invalid in the Select List

When using GROUP BY, there are restrictions on which columns can appear in the SELECT list. For instance, you cannot use a column that isn’t part of the GROUP BY clause or doesn’t have an aggregate function applied to it.

For example, if you try this query:

        
              SELECT StoreName, ProductName, COUNT(*)
FROM Sales
GROUP BY StoreName

You’ll encounter the following error:

        
              Msg 8120, Level 16, State 1, Line 1
Column 'Sales.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

To fix this error, you either need to add ProductName to the GROUP BY clause or use an aggregate function like COUNT():

        
              SELECT StoreName, ProductName, COUNT(*)
FROM Sales
GROUP BY StoreName, ProductName

Alternatively, you can use ProductName with an aggregate function like MAX() to find the product with the highest quantity:

        
              SELECT StoreName, MAX(ProductName), SUM(Quantity)
FROM Sales
GROUP BY StoreName

This query will return the highest-selling product name for each store, along with the total quantity sold.

Tan Lee

How to use GROUP BY in SQL

By Tan Lee Published on Mar 07, 2025 112

GROUP BY Multiple Columns

GROUP BY and NULLs

Error: Column is Invalid in the Select List

Related

Categories

Popular Posts

Entity Framework Code First vs Database First vs Model First Approach

Freedash bootstrap lite

Gentella Admin Template

Spica Admin Dashboard Template

Regal Admin Dashboard Template