The usage of group by grammar in sqlserver

This article introduces the knowledge of "the usage of group by Grammar in sqlserver". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Summarize the usage of group by with examples today.

To 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 SalesInfoselect N' Beijing', '2014-06-09 Zhongri 1001 union allselect N' Beijing', '2014-08-09 Zhi Zhi 1002 union allselect N' Beijing', '2013-10-09 Ying Meng 1009 union allselect N' Dalian', '2013-08-09 Yun Mei 4001 union allselect N' Dalian', '2013-10-09 Zeng Mei 4002 union allselect N' Dalian', '2013-05-12 Zhi 4003 union allselect N' Dalian', '2014-11-11 Yue 4004 union allselect N' Dalian', '2014-12-11 May May 4004 union N' Dalian', '2014-12-11 May

First execute the following script:

Select Ctiy,count (OrderID) as OrderCountfromSalesInfogroup by Ctiywith cube

You can see that the extra line is a summary of all the orders.

Next script:

Select Ctiy,Year (OrderDate) as OrderYear,count (OrderID) as OrderCountfromSalesInfogroup by Ctiy,Year (OrderDate) with cube

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

The next script (notice that rollup appears):

Select Ctiy,Year (OrderDate) as OrderYear,count (OrderID) as OrderCountfromSalesInfogroup by Ctiy,Year (OrderDate) with rollup

Using rollup summarizes the first grouping field listed by group by

Next script:

Select Ctiy,count (OrderID) as OrderCountfromSalesInfowhereCtiy = N' Dalian 'group by all Ctiy

We will see that after using group by all, cities that do not meet the requirements will also appear, but the number of orders is zero.

It should be noted that All cannot be used with cube and rollup. If used with having, the function of All will fail.

Next script:

Select Ctiy,orderdate,orderidfromSalesInfocompute count (orderid)

Two result sets are displayed, one is the order result set, and the other is the total order result set

The last script:

Select Ctiy,orderdate,orderidfromSalesInfoorder by Ctiycompute count (orderid) by Ctiy

The order information of the city is displayed according to the city, and one shows the quantity of all orders in the city.

This is the end of the introduction to the usage of group by Grammar in sqlserver. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!