SQL Server Error Messages - Msg 154 - a COMPUTE clause is not allowed in a SELECT INTO statement.

SQL Server Error Messages - Msg 154

Error Message

Server: Msg 154, Level 15, State 2, Line 1
a COMPUTE clause is not allowed in a SELECT INTO
statement.

The COMPUTE clause of the SELECT statement generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. To generate both subtotals and summary totals, the COMPUTE and COMPUTE BY clause can be specified in the same query.

The aggregate functions (AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, VARP, and SUM), which ignores NULL values, specifies the aggregation to be performed. The <expression> is the name of a column on which the calculation is performed and must appear in the SELECT list and must be specified identical to one of the expressions in the SELECT list. Lastly, the BY <expression> generates control-breaks and subtotals in the result set.

Here’s a script that illustrates the use of the COMPUTE clause of the SELECT statement:

The result will be the same as the ones earlier but with the following additional result set:

sum
-------------
86026932.00

As can be seen, the output contains multiple result sets. Since the COMPUTE clause generates tables and their summary results which are not stored in the database, it cannot be used in a SELECT INTO statement. Including the COMPUTE clause in a SELECT INTO statement will raise this error message, as can be seen in the following SELECT statements:

Msg 154, Level 15, State 5, Line 2
a COMPUTE clause is not allowed in a SELECT INTO statement.

Solution / Work Around:

To generate a summary table, instead of using the COMPUTE/COMPUTE BY clause of the SELECT statement, the GROUP BY should be used instead. According to Books Online, the COMPUTE/COMPUTE BY feature will be removed starting from Microsoft SQL Server 2012.

The following SELECT INTO statement can be used to generate the summary tables:

-- Use GROUP BY to Compute for Summary
SELECT [TeamName], SUM([Salary]) AS [Salary]
INTO [dbo].[NBAPlayerTotalSalaryByTeam]
FROM [dbo].[NBAPlayer]
GROUP BY [TeamName]
ORDER BY [TeamName]
GO

And here’s the result of the query that used the GROUP BY clause of the SELECT statement.

To generate a grand total row in the output similar to the COMPUTE BY and COMPUTE clause shown earlier, the ROLLUP() operator of the GROUP BY can be used. The ROLLUP() generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, as well as a grand total row. Here’s how the SELECT statement query will look like with the ROLLUP option included:

SELECT ISNULL([TeamName], 'All Teams') AS [TeamName], SUM([Salary]) AS [Salary]
INTO [dbo].[NBAPlayerTotalSalaryByTeamWithGrandTotal]
FROM [dbo].[NBAPlayer]
GROUP BY ROLLUP([TeamName])
ORDER BY [TeamName]
GO

And here’s the output of the query, which includes the grand total as one of the rows in the result set:

An alternative to the ROLLUP() operator is the WITH ROLLUP option of the GROUP BY clause. The WITH ROLLUP option specifies that in addition to the usual rows provided by GROUP BY, summary rows will be included in the result set. Unfortunately, the WITH ROLLUP feature of the GROUP BY will soon be removed in a future version of Microsoft SQL Server and use of this feature should be avoided.