|
|
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 syntax of the COMPUTE clause is as follows:
COMPUTE {{ AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }
( <expression> ) } [ ,…n ] [ BY <expression> [ , … n] ]
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:
CREATE TABLE [dbo].[NBAPlayer] (
[NBAPlayerID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[TeamName] VARCHAR(50),
[Salary] MONEY
)
GO
INSERT INTO [dbo].[NBAPlayer] ( [FirstName], [LastName], [TeamName], [Salary] )
VALUES ( 'LeBron', 'James', 'Miami Heat', 19067500 )
GO
INSERT INTO [dbo].[NBAPlayer] ( [FirstName], [LastName], [TeamName], [Salary] )
VALUES ( 'Dwayne', 'Wade', 'Miami Heat', 18673000 )
GO
INSERT INTO [dbo].[NBAPlayer] ( [FirstName], [LastName], [TeamName], [Salary] )
VALUES ( 'Kevin', 'Durant', 'Oklahoma City Thunder', 17832627 )
GO
INSERT INTO [dbo].[NBAPlayer] ( [FirstName], [LastName], [TeamName], [Salary] )
VALUES ( 'Kobe', 'Bryant', 'Los Angeles Lakers', 30453805 )
GO
SELECT [FirstName], [LastName], [TeamName], [Salary]
FROM [dbo].[NBAPlayer]
ORDER BY [TeamName], [Salary] DESC
COMPUTE SUM([Salary]) BY [TeamName]
The output of the SELECT statement is as follows:
FirstName LastName TeamName Salary
---------- --------- ---------------------- ------------
Kobe Bryant Los Angeles Lakers 30453805.00
sum
-------------
30453805.00
FirstName LastName TeamName Salary
---------- --------- ---------------------- ------------
LeBron James Miami Heat 19067500.00
Dwayne Wade Miami Heat 18673000.00
sum
-------------
37740500.00
FirstName LastName TeamName Salary
---------- --------- ---------------------- ------------
Kevin Durant Oklahoma City Thunder 17832627.00
sum
-------------
17832627.00
To include the grand total in the result set, a COMPUTE clause (without the BY) can be added to the query, as follows:
SELECT [FirstName], [LastName], [TeamName], [Salary]
FROM [dbo].[NBAPlayer]
ORDER BY [TeamName], [Salary] DESC
COMPUTE SUM([Salary]) BY [TeamName]
COMPUTE SUM([Salary])
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:
SELECT [FirstName], [LastName], [TeamName], [Salary]
INTO [dbo].[NBAPlayerTotalSalaryByTeam]
FROM [dbo].[NBAPlayer]
ORDER BY [TeamName], [Salary] DESC
COMPUTE SUM([Salary]) BY [TeamName]
Msg 154, Level 15, State 5, Line 2
a COMPUTE clause is not allowed in a SELECT INTO statement.
SELECT [FirstName], [LastName], [TeamName], [Salary]
INTO [dbo].[NBAPlayerTotalSalaryByTeamWithGrandTotal]
FROM [dbo].[NBAPlayer]
ORDER BY [TeamName], [Salary] DESC
COMPUTE SUM([Salary]) BY [TeamName]
COMPUTE SUM([Salary])
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.
SELECT * FROM [dbo].[NBAPlayerTotalSalaryByTeam]
TeamName Salary
---------------------- -------------
Los Angeles Lakers 30453805.00
Miami Heat 37740500.00
Oklahoma City Thunder 17832627.00
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:
SELECT * FROM [dbo].[NBAPlayerTotalSalaryByTeamWithGrandTotal]
TeamName Salary
---------------------- -------------
All Teams 86026932.00
Los Angeles Lakers 30453805.00
Miami Heat 37740500.00
Oklahoma City Thunder 17832627.00
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.
|