SQL entry-level data query tutorial

Introduction & nbsp; in the first two articles, I gave a detailed introduction to the concepts of Single-table and multi-table queries, this article mainly introduces the use of Aggregate functions and Data grouping. & nbsp; Overview & nbsp; simply put, an aggregate function splits multiple rows according to certain rules) a function that aggregates data into one row. After the data is summarized, you can Introduction

In the first two articles, we have made a detailed introduction to the concepts of Single-table queries and multi-table queries. This article mainly introduces the use of Aggregate functions and Data grouping.

Introduction

Simply put, an aggregate function uses multiple rows of data according to certain rules.Summarized into one row. After summarizing the data, you can Group other summarized columns Based on the specified column, and filter by the given conditions again ).

Aggregate functionsSummaryCan be explained as follows:

Simple Aggregate functions

Simple Aggregate functions are functions that intuitively aggregate multiple rows into one Row calculation rule. These functions can often guess the functions from the function name itself, and the parameters of these functions are numerical. Simple Aggregate functions include Avg, Sum, Max, and Min.

The parameters of a simple aggregate function can only beNumberType. In SQL, the numeric types include tinyint, smallint, int, bigint, decimal, money, smallmoney, float, and real.

Before introducing a simple aggregate function, we will first introduce the aggregate function Count.

Count ()

The Count function is used to calculate the number of rows contained in a given condition. For example, the simplest:

In the above table, I want to know the number of employees in the company, which can be used simply:

SELECT     COUNT(*) AS EmployeeNumberFROM         HumanResources.Employee

The result is as follows:

When Count () acts on a specific Column, the difference is that when Count (Column name) encounters a "Null" value, it will not be included, for example:

I want to know the number of employees with superiors in the company:

SELECT     COUNT(ManagerID) AS EmployeeWithSuperiorFROM       HumanResources.Employee

We can see that all employees except the superiors are counted.

It can also be used in Count ().DistinctKeyword to include only one of the same values in each Column, for example:

I want to count the number of managers in the company:

SELECT     COUNT(DISTINCT ManagerID) AS NumberOfManagerFROM       HumanResources.Employee

The result is as follows.

Avg (), Sum (), Max () and Min ()

In addition to different functions, these Aggregate functions have almost the same parameters and usage. So here we will only explain the aggregate function Avg:

Avg () indicates calculating the average value of the summary data within the selected range. During this process, the "Null" value is not counted, for example:

I want to get the average length of vacation for each employee:

SELECT     AVG(VacationHours) AS 'Average vacation hours'FROM       HumanResources.Employee

The result is as follows:

Because the aggregate function is used by default for data aggregation, it does not contain null, but if I want to include a null value, andIn the current queryUse IsNull (column, value)

For example:

I want to obtain the average length of vacation for each employee. If the employee does not take a vacation, it is calculated as 10 hours on leave.

SELECT     AVG(ISNULL(VacationHours, 10)) AS  'Average vacation hours'FROM       HumanResources.Employee

The result is as follows:

You can also use the DISTINCT keyword to make each value unique in a simple aggregate function to participate in the aggregation summary operation. It has been explained in the Count function above and will not be repeated here.

And aboutSum (), Max (), Min ()The usage of these simple Aggregate functions is basically the same and will not be repeated here.

Group the values obtained by the aggregate function by Column

If the aggregate function results cannot be grouped by specific values, then the aggregate function is less powerful. In SQL, useGroupGroup the Aggregate functions. The concept of grouping can be expressed in the following simple example:

For example:

I want to get the total sales by sales personnel in different provinces:

SELECT     TerritoryID, SUM(SalesLastYear) AS ToTalSalesFROM       Sales.SalesPersonGROUP BY   TerritoryID

Shows the concept:

FollowGroupThe column names are the basis for grouping. Of course, in some cases, grouping is performed based on multiple columns. The following example has some practical significance:

I want to get the total sick leave time for employees of different managers based on different gender:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumberFROM       HumanResources.EmployeeGROUP BY   Gender, ManagerID

The result is as follows:

The Group By clause is followed By multiple columns. In logic thinking, we can perform this operation based on the unique ManagerId and unique Gender of each column.Cross Join(If you do not understand Cross join, please refer to my previous article) Obtain a unique Key that can be used to determine other keys, and finally filter out rows that cannot be returned in aggregate functions (Row) (that is, Null) rows. Based on the actual two columnsLogicThe above is the value of a column as the basis for grouping.

We can see that we first group according to the manager ID, then according to the gender of the employees under Different managers, and then divide the total again, and finally get the sum of the sick leave time according to the grouping conditions.

Note that when usingGroupWhen grouping by multiple columns, be sure to appear inGroupSubsequent order

In the preceding example, Gender first traverses all possible values of Gender, computes matching ManagerID Based on the possible values of each Gender, and then performs aggregate function operations based on ManagerID, if you change the Column order after Group By to ManagerId, and then Gender, it means that you traverse all possible values of ManagerID and then match Gender. The result is as follows:

Starting from Gender (Gender) to M (male), the second traversal of ManagerId for matching:

We can see from the above that, although the order of columns (columns) behind the Group by clause is different and the order of the results is different, the obtained DataSet (DataSet) is exactly the same, therefore, the Order By clause can be used to query the Group By statement in different column Order to obtain the same results. Here it is no longer.

Filter the dataset After grouping (Having) again)

After grouping with Aggregate functions, you can useGroupClauseHavingClause filters the grouped data again.HavingClause is similarWhereFor more information about how to use the having expression, see where in my previous article. The Having clause can be understood as a statement that performs Secondary Filtering After grouping.

Having clause is very simple, but it should be noted that the having clause cannot be followed by the alias in the statement, but the expression in the Select statement must be written again, for example, for the above table:

I want to get the total sick leave time of employees under Different managers according to different gender. The employees under these managers need more than two persons:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumberFROM       HumanResources.EmployeeGROUP BY   ManagerID, GenderHAVING     (EmployeeNumber > 2)

Note:The preceding statement is incorrect. aliases or variable names cannot be referenced after the Having clause. To achieve the preceding effect, you need to Count (*) this expression is rewritten in the Having clause. The correct syntax is as follows:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumberFROM       HumanResources.EmployeeGROUP BY   ManagerID, GenderHAVING     (COUNT(*) > 2)

The result is as follows:

We can see that only the condition with more than two employees is selected.

Of course, the Having clauseThe most powerful thing is that it can use aggregate functions as expressions., Which is not allowed in the Where clause. The following example demonstrates the power of the Having clause:

Or the data in the example above:

I want to get the total number of sick leave times for employees under Different managers, and the number of sick leave hours for employees under this manager is twice the number of sick leave employees:

SELECT     ManagerID, SUM(SickLeaveHours) AS TotalSickLeaveHours, COUNT(*) AS EmployeeNumberFROM       HumanResources.EmployeeGROUP BY   ManagerIDHAVING      (MAX(SickLeaveHours) > 2 * MIN(SickLeaveHours))

The result is as follows:

It can be seen that Having clauses can implement powerful functions in such a simple way, and it will be very troublesome to use where clause. In the above result, the scope of the aggregate function of the having statement can be well demonstrated:

As shown in the preceding figure, the number of hours for the most frequently asked employees on leave is significantly greater than twice the number of hours for the minimum number of employees on leave.

Summary

Starting with the concept of Aggregate functions, this article describes the frequently-used queries, grouping, and filtering methods used by Aggregate functions. A good use of Aggregate functions can be used to transfer many tasks placed on the application business layer. This will be of great help for maintenance and performance improvement.