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.