SQL Server Error Messages - Msg 158

Error Message

Server: Msg 158, Level 16, State 1, Line 1
An aggregate may not appear in the OUTPUT clause.

Causes

The OUTPUT clause, introduced in SQL Server 2005, returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement.

These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

One of the restrictions when using the OUTPUT clause is that aggregate functions are not permitted; otherwise this error message will be encountered.

To illustrate how this error can be generated, given the following table definition:

CREATE TABLE [dbo].[Products](
    [ProductID]     [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductName]   [varchar](50) NULL,
    [UnitPrice]     [money] NULL,
    [Quantity]      [int] NULL
)

This table contains all the products being sold by your company. As a sales promotion, the company has decided that the price of all products will be reduced by 10%. After reducing the price of all products, you want to output the old and new minimum price, old and new maximum price, and the old and new average price. Executing the following UPDATE statement with the OUTPUT clause, which updates the products table by reducing the unit price of all products by 10% and returning the data requested generates this error message:

UPDATE [dbo].[Products]
SET [UnitPrice] = [UnitPrice] * 0.9
OUTPUT MIN([deleted].[UnitPrice]) AS [OldMinimumPrice],
       MIN([inserted].[UnitPrice]) AS [NewMinimumPrice],
       MAX([deleted].[UnitPrice]) AS [OldMaximumPrice],
       MAX([inserted].[UnitPrice]) AS [NewMaximumPrice],
       AVG([deleted].[UnitPrice]) AS [OldAveragePrice],
       AVG([inserted].[UnitPrice]) AS [NewAveragePrice]

Msg 158, Level 15, State 1, Line 3
An aggregate may not appear in the OUTPUT clause.

Solution / Work Around

As mentioned earlier, and as the message suggests, aggregate functions are not permitted in the OUTPUT clause. To overcome this restriction, one thing that can be done is to first put in a new table, whether a temporary table or a table variable, the columns used in the aggregate functions. In the scenario shown earlier, this would be the old and new unit price for all products. Then once the updated data has been inserted into a table, the required information can now be generated, which is in this case the old and new minimum price, old and new maximum price and the old and new average price.

The following script can be used to replace the UPDATE statement earlier which returns all the required information after the UPDATE on the unit price.

DECLARE @ProductPrices TABLE (
    [ProductID]         INT,
    [OldPrice]          MONEY,
    [NewPrice]          MONEY
)

UPDATE [dbo].[Products]
SET [UnitPrice] = [UnitPrice] * 0.9
OUTPUT [inserted].[ProductID], 
       [deleted].[UnitPrice],
       [inserted].[UnitPrice] INTO @ProductPrices

SELECT MIN([OldPrice]) AS [OldMinimumPrice],
       MIN([NewPrice]) AS [NewMinimumPrice],
       MAX([OldPrice]) AS [OldMaximumPrice],
       MAX([NewPrice]) AS [NewMaximumPrice],
       AVG([OldPrice]) AS [OldAveragePrice],
       AVG([NewPrice]) AS [NewAveragePrice]
FROM @ProductPrices

The first part of this script is the definition of the table variable which will store the updated information from the products table. Although not used in the final output, the ProductID is included in this table just for reference and just in case there will be a different requirement that will involve individual products. Otherwise, the ProductID can be removed from this table variable.

After the definition of the table variable, the actual UPDATE statement is executed. Included in the UPDATE statement is the OUTPUT clause which inserts the product ID, old unit price and new unit price to the table variable for all products updated. Since there is no WHERE clause in this particular UPDATE statement, all products will be updated and will be inserted in the table variable.

Lastly, the required outputs are returned by the SELECT statement where the aggregate functions MIN, MAX and AVG can be used against the old and new unit prices.