SQL GROUP BY group query

This article is guided by: in the actual SQL application, the group aggregation is often needed, the query object is grouped according to certain conditions, and then the aggregation of each group is analyzed. The creation of groupings is achieved through the GROUP BY clause. Unlike the WHERE clause, the GROUP by clause is used to summarize the information type, summarizing the relevant data. The role of GROUP by IS to divide a data set into small areas by certain rules and then data processing for several small areas.

A grouped query used in SQL Server is an ORDER BY clause that uses an ORDER BY clause to work with an aggregate function to complete a group query, in the field of a select query if the field does not use an aggregate function, it must appear in the order By clause (that is, the field name behind the select either appears in the aggregate function or is used in the ORDER BY clause)

You can also use the HAVING clause in a grouped query to define a query condition.

Use GROUP by to group queries

When using the group by keyword, the items that can be specified in the select list are limited, and only the following items are allowed in the SELECT statement:

The columns that are grouped

Returns a worthwhile expression for each grouping, such as an aggregate function with a column name as a parameter

Group by has a principle that all columns following the Select Do not have columns that use aggregate functions and must appear behind group by (important)

Group BY instance

Instance One

Data sheet:

Name Account Score

Zhang San Language 80

Zhang San Mathematics 98

Zhang San English 65

John Doe Language 70

John Doe Mathematics 80

John Doe English 90

Expected query Results:

Name Chinese maths English

Sheet 380 98 65

Lee 470 80 90

Code

SQL Code Replication

CREATE TABLE Testscore

(

TID int primary key identity (.),

Tname varchar (+) NULL,

Ttype varchar (TEN) NULL,

Tscor int NULL

)

Go

---inserting data

INSERT into Testscore values (' Zhang San ', ' language ', 80)

INSERT into Testscore values (' Zhang San ', ' math ', 98)

INSERT into Testscore values (' Zhang San ', ' English ', 65)

INSERT into Testscore values (' John Doe ', ' language ', 70)

INSERT into Testscore values (' John Doe ', ' math ', 80)

INSERT into Testscore values (' John Doe ', ' English ', 90)

Select Tname as ' name ',

Max (case ttype "language" then Tscor else 0 end) ' Language ',

Max (case ttype when ' math ' then Tscor else 0 end) ' Math ',

Max (case ttype when ' English ' then Tscor else 0 end) ' English '

From Testscore

GROUP BY Tname

Example Two

There is the following data: (in order to see more clearly, I did not use the country code, but directly with the country name as primary Key)

Country (country)	Population (population)
China	600
United States	100
Canada	100
United Kingdom	200
France	300
Japan	250
Germany	200
Mexico	50
India	250

According to the population data of this country, the population of Asia and North America is counted. The following result should be obtained.

Chau	Population
Asia	1100
North america	250
Other	700

Code

SQL Code Replication

SELECT SUM (population),

Case Country

When ' China ' then ' Asia '

When ' India ' Then ' Asia '

When ' Japan ' then ' Asia '

When ' America ' Then ' North America '

When ' Canada ' then ' North America '

When ' Mexico ' then ' North America '

Else ' other ' END

From Table_a

GROUP by Case Country

When ' China ' then ' Asia '

When ' India ' Then ' Asia '

When ' Japan ' then ' Asia '

When ' America ' Then ' North America '

When ' Canada ' then ' North America '

When ' Mexico ' then ' North America '

Else ' other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows;

SQL Code Replication

SELECT

Case when salary <= ' 1 '

When salary > Salary <= 2 '

When salary > Salary <= 3 '

When salary > Salary <= "4"

ELSE NULL END Salary_class,

COUNT (*)

From Table_a

GROUP by

Case when salary <= ' 1 '

When salary > Salary <= 2 '

When salary > Salary <= 3 '

When salary > Salary <= "4"

ELSE NULL END;

For GroupBy, it is usually followed by a column name, but in this case the grouping is made stronger by the cases statement.

Example Three

Have the following data

Country (country)	Gender (Sex)	Population (population)
China	1	340
China	2	260
United States	1	45
United States	2	55
Canada	1	51
Canada	2	49
United Kingdom	1	40
United Kingdom	2	60

Grouped according to country and gender, the results are as follows

Countries	Man	Woman
China	340	260
United States	45	55
Canada	51	49
United Kingdom	40	60

Code

SQL Code Replication

SELECT Country,

SUM (case if sex = ' 1 ' Then

Population ELSE 0 END),--Male population

SUM (case if sex = ' 2 ' Then

Population ELSE 0 END)--Female population

From Table_a

GROUP by country;

Null value handling in the GROUP by clause

How do I group when null values appear in a column in a GROUP BY clause that is used for grouping? In SQL, NULL is not equal to null (described in the WHERE clause). However, in the GROUP BY clause, all the null values are divided into the same group, that is, they are considered "equal".

HAVING clause

GROUP BY clauses are grouped by simply grouping the data of the selected column into a group of rows that have the same value. In practical applications, it is often necessary to delete rows that do not meet the criteria, and in order to achieve this, SQL provides a HAVING clause. The syntax is as follows.

SELECT column, SUM (column)

From table

GROUP by column

Having SUM (column) condition value

Description: The having is usually used in conjunction with the GROUP BY clause. Of course, the sum () function in the syntax can also be any other aggregate function. The DBMS applies the search condition in the HAVING clause to the row group produced by the GROUP BY clause and removes it from the result table if the row group does not meet the search criteria.

Application of HAVING clause

Check the teacher table for at least two teachers and the number of faculty members.

Implementation code:

SQL Code Replication

SELECT dname, COUNT (*) as Num_teacher

From TEACHER

GROUP by Dname

Having COUNT (*) >=2

The difference between a HAVING clause and a WHERE clause

The HAVING clause and the WHERE clause are similar in that it also defines the search criteria. Unlike the WHERE clause, however, a HAVING clause is related to a group, not to a single row.

1. If a GROUP BY clause is specified, the search condition defined by the HAVING clause will act on those groups created by the GROUP BY clause.

2. If you specify a WHERE clause without specifying a GROUP BY clause, the search condition defined by the HAVING clause will act on the output of the WHERE clause and treat the output as a group.

3. If neither the GROUP BY clause nor the WHERE clause are specified, the search condition defined by the HAVING clause will act on the output of the FROM clause and treat the output as a group.

4. In the SELECT statement, the WHERE and having clauses are executed in different order. In this book, the. 1.2 section describes the execution steps of the SELECT statement that the WHERE clause can only receive input from the FROM clause, while the HAVING clause receives input from the GROUP BY clause, the WHERE clause, and the FROM clause.

5)

-Group by .... with rollup

CREATE TABLE #test (

Name varchar (10)

, [Procedure] CHAR (1)

, model varchar (5)

, quantity int);

INSERT into #testSELECT ' A ', ' 1 ', ' φ ', 500

Union Allselect ' A ', ' 1 ', ' φ ', 600

Union Allselect ' A ', ' 1 ', ' φ ', 500

Union Allselect ' A ', ' 2 ', ' φ ', 700

Union Allselect ' A ', ' 2 ', ' φ ', 200

Union Allselect ' B ', ' 1 ', ' φ ', 1000;

SELECT

Case if GROUPING (Name) = 1 Then ' total '

When GROUPING (name) = 0 and GROUPING ([procedure]) = 1 then Name + ' total '

When GROUPING (Name) = 0 and GROUPING ([procedure]) = 0 and GROUPING ([model]) = 1 then Name + ' + [Procedure] + ' subtotal '

Else name End as Name

GROUPING ([model]) = 1 Then ' else [procedure] end as [procedure]

, IsNull (model, ") as model

, sum (quantity) as quantity

From #test

Group by Name, [procedure], model with rollup

Procedure Model Quantity-----------------------------------------

-A 1φ500

A 1φ1100

Subtotal of a 1600

A 2φ200

A 2φ700

Subtotal of a 900

A total of 2500

B 1φ1000

Subtotal of B 1000

B Total 1000

Total 3500

The cube operator produces a result set that is a cube, which is an extension of the fact data that records data for an individual time, and the extension is built on the columns that the user prepares to analyze, which are called dimensions, and the cube is a result set that contains all possible cross tables for each latitude.

The cube operator is specified in the GROUP BY clause of the SELECT statement, and group by should specify the dimension column and the keyword with cube, and the result set will include all possible combinations of values in the dimension column.

Example 1.

The SQL statements are as follows:

SELECT * FROM Student

Select Sex,sclass,sum (score) as Total

From student

Group by Sex,sclass with Cube

Select Sex,sclass,sum (score) as Total

From student

Group by Sclass,sex with Cube

SQL query is run like this:

1. The first gender of the gender is male, then the boys are queried first, then the class

2. When the query is complete, the data for sex as male is aggregated

3. Query gender for female data, the same as the total after the query is completed

4. Total aggregation without distinction of gender and class

5. All of the above are categorized by gender, as all summaries of gender are completed

6. Follow the Sclass to group the totals.

Attention:

1. The classification is based not on the order in the Select, but on the order in group by.

2. Try to match the order of the fields in the Select and group by so that the display looks more comfortable and specific analysis.

For the results of the above query, we can see that there is a vacancy problem in the data, after binding to the GridView display as follows:

This effect does not meet the needs of the actual project, so we should improve the SQL statement.

Use grouping to differentiate between null values.

How to distinguish between null values resulting from using the cube and Null values in the actual query. This problem can be solved with the grouping function. If the value in the column comes from the query data, grouping returns 0 if the value in the column is a null value from cube, 1 is returned.

Example 2.

SQL is as follows:

Select Case [Grouping (Sex) =1) Then ' little ' else sex

End as gender,

Case if (grouping (sclass) =1) Then ' little ' else sclass

End as class,

SUM (Score)

From student

Group by Sex,sclass with Cube

The following appears on the page:

Cube can generate an n-dimensional cube, which is a cube with any dimensional purpose, and a cube with only one dimension can be used to generate totals.

Example 3:

Sql:

Select Case when (Grouping (sex) =1) Then ' total ' else sex end as gender,

SUM (score) as Total

From student

GROUP by sex with Cube

The result of a data collection that produces many dimensions can be large, by generating a large view and selecting the display.

Article Source: http://www.studyofnet.com/news/247.html

SQL GROUP BY group query