To sum up the usage of sqlserver group by, sqlservergroup

To sum up the usage of sqlserver group by, sqlservergroup

Today, we will summarize the usage of group by with examples.

To sum up, group by: ALL, Cube, RollUP, Compute, Compute

Create data script

Create Table SalesInfo
(Ctiy nvarchar (50 ),
OrderDate datetime,
OrderID int
)

Insert into SalesInfo
Select N 'beijing', '2017-06-09 ', 2014
Union all
Select N 'beijing', '2017-08-09 ', 2014
Union all
Select N 'beijing', '2017-10-09 ', 2013
Union all
Select N 'dalian ', '2017-08-09', 2013
Union all
Select N 'dalian ', '2017-10-09', 2013
Union all
Select N 'dalian ', '2017-05-12', 2013
Union all
Select N 'dalian ', '2017-11-11', 2014
Union all
Select N 'dalian ', '2017-12-11', 2014

Run the following script:

Select Ctiy, count (OrderID) as OrderCount
From
SalesInfo
Group by Ctiy
With cube

We can see that an additional row is a summary of all the orders.

Next script:

Select Ctiy, Year (OrderDate) as OrderYear, count (OrderID) as OrderCount
From
SalesInfo
Group by Ctiy, Year (OrderDate)
With cube

We can see that all dimensions in the group are summarized, and there is a sum of 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

Rollup is used to summarize the first group field listed by group.

Next script:

Select Ctiy, count (OrderID) as OrderCount
From
SalesInfo
Where
Ctiy = N 'dalian'
Group by all Ctiy

We can see that when group by all is used, cities that do not meet the conditions will also appear, but the number of orders is zero.

Note that All cannot be used with cube and rollup. If it is used with having, the All function will be invalid.

Next script:

Select Ctiy, orderdate, orderid
From
SalesInfo
Compute count (orderid)

Two result sets are displayed. One is the order result set and the other is the result set of the total number of orders.

Last script:

Select Ctiy, orderdate, orderid
From
SalesInfo
Order by Ctiy
Compute count (orderid) by Ctiy

Displays the order information of the city according to different cities, and displays the number of all orders of the city.

Let's talk about this first.

SQL server group by usage

Group by is a commonly used syntax in SQL Server. The syntax is as follows:
[Group by [ALL] group_by_expression [,... n] [WITH {CUBE | ROLLUP}]
1. The most common syntax is as follows:
Select CategoryID, AVG (UnitPrice), COUNT (UnitPrice) FROM dbo. Products Where UnitPrice 30 group by CategoryID order by CategoryID DESC
This statement queries the average unit price and unit price of all products. And the unit price is more than 30 records.
2. Let's look at this syntax as follows:
Select CategoryID, AVG (DISTINCT UnitPrice), COUNT (DISTINCT UnitPrice) FROM dbo. Products Where UnitPrice 30 group by CategoryID order by CategoryID DESC
When DISTINCT is used, the average unit price of repeated prices is removed.
3. If you want to use conditional filtering after classification statistics, the following statement can be used AS the parameter: Select CategoryID, SUM (UnitPrice) AS SumPriceFROM dbo. productsGROUP BY CategoryIDHAVING SUM (UnitPrice) 300 HAVING is similar to the Where statement. Where is used before classification, while HAVING is used after classification. It uses AND, OR, NOT, and like in the same way as Where.
4. If you want to add a summary row in classification statistics, you can use the following statement:
Select CategoryID, SUM (UnitPrice), GROUPING (CategoryID) AS 'grouping' FROM dbo. ProductsGROUP BY CategoryID WITH ROLLUP
The Grouping column is used to identify which row is a summary row. It uses the ROLLUP operation to add a summary row.
5. If you use with cube, a multi-dimensional classification dataset is generated as follows:
Select CategoryID, SupplierID, SUM (UnitPrice) AS SumPriceFROM dbo. ProductsGROUP BY CategoryID, SupplierID WITH CUBE
It generates a crosstab chart and generates a summary of all possible combinations.
6. Using rollup cube produces a NULL value. You can use the following syntax to solve the problem:
Select case when (GROUPING (SupplierID) = 1) THEN '-1' ELSE SupplierID end as SupplierID, SUM (UnitPrice) AS QtySumFROM dbo. productsGROUP BY SupplierID with cube it first checks whether the current row is a summary row. If yes, you can set a value. Here it is set to '-1 '.

Group by statement in SQL server2005

Group by is the meaning of group display. It must be used together with statistical statements, such as sum.

If you want to sort data, you should use order by gender.

If you want to use group by, you can use the following command to count the number of male and female students

Select gender, count (*) from cj group by gender