SQL Server Error 8120: Column Is Not in GROUP BY

SQL Server Msg 8120 means a SELECT expression refers to a column that is neither aggregated nor included in the GROUP BY list. For a grouped query, every nonaggregate column or expression in the select list must be represented in GROUP BY. See Microsoft’s GROUP BY documentation.

Example of the error

This query asks for a total per customer while also selecting each order date:

SELECT CustomerID, OrderDate, SUM(Amount) AS total_amount
FROM dbo.Orders
GROUP BY CustomerID;

OrderDate is neither grouped nor aggregated, so SQL Server raises Msg 8120. The query does not define which order date should represent all of a customer’s orders.

Choose the intended result grain

If the result should contain one row per customer and order date, include both columns in GROUP BY:

SELECT CustomerID, OrderDate, SUM(Amount) AS total_amount
FROM dbo.Orders
GROUP BY CustomerID, OrderDate;

This changes the result to one row per customer/date combination. If the result should stay one row per customer, aggregate OrderDate only when a particular summary is meaningful, such as the first order date:

SELECT CustomerID,
       MIN(OrderDate) AS first_order_date,
       SUM(Amount) AS total_amount
FROM dbo.Orders
GROUP BY CustomerID;

Do not add MIN() or MAX() merely to silence the error; choose an aggregate that matches the question the report is meant to answer.

Keep detail rows with a window aggregate

If the output needs every order row alongside the customer’s total, use a window aggregate instead of grouping away detail rows:

SELECT OrderID,
       CustomerID,
       OrderDate,
       Amount,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS customer_total
FROM dbo.Orders;

This returns each order and repeats its customer’s total on each corresponding row. See the SQL Server aggregate function reference for SUM(), AVG(), and other aggregates.

WHERE filters input rows before groups are calculated. Use HAVING to filter groups based on aggregate results; a raw aggregate expression does not belong in WHERE.

Browse the SQL Server error troubleshooting index for other message guides.