Summarize the usage of SQL Server GROUP by _mssql

Today, use the example to summarize the usage of group by.

Sum up: Group By:all, Cube,rollup,compute,compute by

Create a data script

Create Table Salesinfo
(ctiy nvarchar (50),
OrderDate datetime,
OrderID int
)

INSERT INTO Salesinfo
Select N ' Beijing ', ' 2014-06-09 ', 1001
UNION ALL
Select N ' Beijing ', ' 2014-08-09 ', 1002
UNION ALL
Select N ' Beijing ', ' 2013-10-09 ', 1009
UNION ALL
Select N ' Dalian ', ' 2013-08-09 ', 4001
UNION ALL
Select N ' Dalian ', ' 2013-10-09 ', 4002
UNION ALL
Select N ' Dalian ', ' 2013-05-12 ', 4003
UNION ALL
Select N ' Dalian ', ' 2014-11-11 ', 4004
UNION ALL
Select N ' Dalian ', ' 2014-12-11 ', 4005

First execute the following script:

Select Ctiy,count (OrderID) as OrderCount
From
Salesinfo
GROUP BY Ctiy
With Cube

You can see that one more row is a summary of all the order numbers

Next script:

Select Ctiy,year (OrderDate) as Orderyear,count (OrderID) as OrderCount
From
Salesinfo
Group by Ctiy,year (OrderDate)
With Cube

You can see that the dimensions in the group are summarized, and there is also a sum of the orders

Next script (note that rollup appears):

Select Ctiy,year (OrderDate) as Orderyear,count (OrderID) as OrderCount
From
Salesinfo
Group by Ctiy,year (OrderDate)
With rollup

Use rollup to summarize the first grouped field listed in Group by

Next script:

Select Ctiy,count (OrderID) as OrderCount
From
Salesinfo
where
ctiy = N ' Dalian '
GROUP BY all Ctiy

We will see that cities that do not meet the criteria will also appear when you use GROUP by all, except that the number of orders is zero

Note that all cannot be used with cube and rollup, and that all functions are invalidated when used together.

Next script:

Select Ctiy,orderdate,orderid
From
Salesinfo
Compute COUNT (OrderID)

Shows two result sets, one for order result sets and one for total order result set

Last script:

Select Ctiy,orderdate,orderid
From
Salesinfo
ORDER BY Ctiy
Compute COUNT (OrderID) by ctiy

According to different cities, displays the order information of the city, a display of all orders for the city

That's all for the first.