If you’re getting an error that reads “An aggregate may not appear in the OUTPUT clause” in SQL Server, it appears that you’re using an aggregate function in the OUTPUT clause.

Aggregate functions are not allowed in the OUTPUT clause.

To fix this issue, remove any aggregate functions from the OUTPUT clause and try applying them to a table or table variable.

Example of Error

Here’s an example of code that produces the error:

-- Create table
CREATE TABLE t1 (
    c1 INT, 
    c2 INT
    );

-- Insert data
INSERT INTO t1
OUTPUT 
    INSERTED.c1,
    INSERTED.c2,
    SUM(INSERTED.c2) AS TotalAmount
VALUES 
    ( 1, 354 ),
    ( 2, 740 ),
    ( 3, 198 );

Output:

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

The error is self-explanatory – aggregate functions cannot appear in the OUTPUT clause. In this example I used the SUM() function, which is an aggregate function.

Solution 1

The most obvious way to get rid of the error is to remove the aggregate function. Here’s an updated version of the INSERT statement without the aggregate function:

INSERT INTO t1
OUTPUT 
    INSERTED.c1,
    INSERTED.c2
VALUES 
    ( 1, 354 ),
    ( 2, 740 ),
    ( 3, 198 );

Output:

CustomerID  FirstName  LastName
----------  ---------  --------
1           Rohit      Rooney  
2           Selina     Bradey  
3           Nelly      Nguyen  

This time it worked.

Solution 2

Another option is to insert the OUTPUT values into a table variable (or even a table), and then perform the aggregate function against that:

-- Create a table variable to store output
DECLARE @InsertedItems TABLE (
    c1 INT, 
    c2 INT
    );

-- Insert data
INSERT INTO t1
OUTPUT 
    INSERTED.c1,
    INSERTED.c2
    INTO @InsertedItems
VALUES 
    ( 1, 354 ),
    ( 2, 740 ),
    ( 3, 198 );

-- Now use the aggregate function against the table variable
SELECT
    SUM(c2) AS TotalAmount
FROM @InsertedItems;

Output:

TotalAmount
-----------
1292       

It’s true that we could have simply selected from the t1 table itself, because these would be the only rows in that table (due to it being our first INSERT into that table), but using this method will cater for scenarios where the table may already contain data but we only want to apply the aggregate function to the new data.