SQL Server Compute/ComputeBy for data aggregation

Create a table Material to test CREATETABLEmaterial (pidINTPRIMARYKEYIDENTITY (100), nameVARCHAR (), colorVARCHAR (50), numINT); insert some tests: INSERTINTOmaterial (name, color, num) SELECTball, red, 12 UNIONALLSELECTball, red,

CREATE a TABLE Material to test create table material (pid int primary key identity (100), name VARCHAR (), color VARCHAR (50), num INT); insert a test: insert into material (name, color, num) SELECT 'ball', 'red', 12 union all select 'ball', 'red ',

Create a table Material for testing

Create table material (
Pid int primary key identity (1, 1 ),
Name VARCHAR (100 ),
Color VARCHAR (50 ),
Num INT
);

Insert some test values:

Insert into material (name, color, num)
SELECT 'ball', 'red', 12
UNION ALL
SELECT 'ball', 'red', 24
UNION ALL
SELECT 'cat', 'yellow', 13
UNION ALL
SELECT 'ball', 'yellow', 13
UNION ALL
SELECT 'dog ', 'yellow', 13
UNION ALL
SELECT 'cat', 'black', 4
UNION ALL
SELECT 'cat', 'white', 5

(1) Compute

Select name, color, num
FROM material
Order by name, color
Compute sum (num), MAX (num)

Result:

(2) Compute

Select name, color, num
FROM material
Order by name, color
Compute sum (num), MAX (num) by name, color

Result:

Summary:

Group by generates a single result set. Each group has a row that only contains the group's aggregate functions based on the column and display the group's sub-aggregation. The selection list can only contain groups based on columns and Aggregate functions.


COMPUTE generates multiple result sets. A type of result set contains the detailed rows of each group, including the expressions in the selection list. Another type of result set includes the sub-aggregation of the group, or the total aggregation of SELECT statements. The selection list can contain expressions other than grouping based on columns or aggregate functions. The aggregate function is specified in the COMPUTE clause, rather than in the selection list.

Note: In the COMPUTE or compute by clause, ntext, text, or image cannot be included.DataType.