SQL server--Group queries (methods and ideas)

Thought

Sort first In summary

group queries in SQL Server are typically used in conjunction with aggregate functions to achieve the information of subtotal statistics. In fact, the essence of the classification is to sort the information first, sort the information of the same category together, and then calculate the statistic by the demand.

Use GROUP by to group queries

Example Demo

-- Check the number of male and female students

Before learning to group queries, we can install the general idea to solve the query requirements:

Select COUNT (*) from student where sex= ' man '

Select COUNT (*) from student where sex= ' female '

So now it's time to find the total number of people in each class

If you follow the usual resolution of queries, then we should consider:

1. Each class, we do not know which classes in the table, then we where the conditions of how to write?

2. If there are 1000 classes in the table, do I have to write 1000 where query statements?

In the face of such a problem SQL Server has prepared GROUP BY Keyword Implementation Group query

Before using the introduction of the group BY keyword, let's take a look at the principle and logic of the system to implement packet queries:

Map to SQL statement:

Select Sex,count(*) as   number from  Groupby Sex

This short statement, after the principle, should know that the first step is to query all the information from the table, and then group by the field after grouping in the statistical summary

The above cases are counted through the count () function, and of course grouped summaries can also be aggregated using other aggregate functions.

Use the GROUP BY statement Note one:

The graph uses a query statement that contains a field and an aggregate function. Why did you get an error?

Why, we separate the two fields to query:

Summary: in order to ensure completeness, the system has been established, in the use of aggregate functions in the query statement, in addition to the aggregate function, you can be on the query list, to appear in other fields, the field must be a grouping field, and the field is bound to follow the group by keyword.

The columns that appear in the query after the select with the aggregation function have only two possibilities: aggregated, grouped

Multi-column Group query

-- instance requirements, query each class, the number of male and female students

Analysis: Obviously needs are two groups, each class needs to divide the group, the male and female also need to group

Implementation diagram:

SQL statements:

Select  Gradeid,sex,COUNT(*) from the  Groupbyorder by Gradeid

Experience: Figuring out what needs to be divided into groups, figuring out the order of the groupings, round-by-round points, the system is divided into groups before the summary information

Use the HAVING clause

Demo Example: --Check the number of men and women in each class, and only need to show a record of more than 3 people

Analysis: This query is clearly filtering the multicolumn grouping of the previous example.

Try to solve this query requirement within the knowledge range ....

First, then we will follow the where we learn to filter

Results

This error tells us that the aggregation function cannot be used as a condition in where it fails!

Second, since the aggregation function cannot be used, then give the aggregate function an alias, let it be a column

Results

Cause of error: The cause is where the source data is filtered, that is, to filter the table after from, since it is the source data, then where to go to the source data table to find an alias field, how can there be! That's why it's an error.

Let's explore why it's not possible to use where

1. The requirement is to filter the data set after grouping, where it is only filtered for the data table raw data

The 2.where keyword can appear only once, and according to the order of the query, the result set that is obtained after the Where condition is filtered first, in the group by group

Workaround, use the HAVING keyword:

Having the core: it is the result set after the grouping statistic, the data is filtered

SQL statements:

Select  Gradeid,sex,COUNT(*) as from    student  Group  by Gradeid,sex having  Count(*)>=3order  by Gradeid

where is different from having:where is filtering the raw data, having a filter on the data at the time of grouping

Order of execution of query statements

This leads to the execution order of the key words in the query in the query mechanism.

Query Keyword family members

Select  Top / distinct  from where Group  by  having  Filter the  order by sort field list after a grouped result set

Execution order:

1.from

2.where

3.group by

4.having

5. Display the result set according to the fields to be displayed after the Select key

6.order by to sort the final result set

7.top/dictinct

SQL server--Group queries (methods and ideas)