How to use GROUP BY in SQL

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

Sales Table:

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:

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:

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.