Shulou(Shulou.com)05/31 Report--

This article mainly explains "SQL grouping function group by and aggregate function COUNT, MAX, MIN, AVG, SUM what is / what is the usage", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what is the SQL grouping function group by and aggregate functions COUNT, MAX, MIN, AVG, SUM.

1 reasons for grouping aggregation

SQL grouping function and aggregate function have been introduced in previous articles, it is possible to understand these two functions alone, grouping function is group by, aggregate function is COUNT, MAX, MIN, AVG, SUM.

Explain the data in the above figure. Suppose you group the data according to the product_type field, and the result is as follows.

SELECT product_type from productgroup by product_type

It can be seen from the picture that it is divided into three groups, namely, kitchen utensils, clothes and office supplies, which is equivalent to removing the weight of the product_type field, and it is true that the group by function has the function of removing weight.

SELECT DISTINCT product_type from product

Suppose that after grouping, I want to look at the price, that is, the value of the sale_price field, which is written as follows, and will report the following error.

SELECT product_type,sale_price from productgroup by product_type

Why is that? After the original table is grouped according to product_type, kitchen appliances correspond to 4 values, clothes correspond to 2 values, and office supplies correspond to 2 values. This is why an error was reported when taking the sale_price field. Multiple values cannot be filled in a space. At this time, aggregate functions can be used, such as summation, averaging, maximum and minimum values, and the number of lines. There is only one value after aggregation.

SELECT product_type,sum (sale_price), avg (sale_price), count (sale_price), max (sale_price) from productgroup by product_type

The principle is the same for grouping multiple fields. Remember two points from the above: group deduplication and group aggregation.

2 the difference between distinct and group by weight removal Distinct and group by are designed with different emphasis.

Distinct is just to remove weight, while group by is to aggregate statistics.

Both of them have the effect of removing weight, but the efficiency of execution is different.

Single field de-duplication

-- DISTINCTSELECT distinct product_type from product--GROUP BYselect product_type from productGROUP BY product_type

Multiple fields are de-duplicated

-- DISTINCTSELECT distinct product_name, product_type from product--GROUP BYselect product_name, product_type from productGROUP BY product_name, product_type

Execution efficiency

Select, fromwhere query conditions group by grouping categories having specifies conditional order by (desc) limit numbers for grouping results

The running order of the SQL language, first perform the first step in the above figure, then execute the select clause, and finally filter the results. Distinct is in the select clause, while group by is in the first step, so group by de-duplication is more efficient than distinct de-duplication.

Aggregate function and grouping function in sql _ SQL Select count aggregate function-Grammar example explanation

Aggregate function and grouping function in sql

The COUNT operator is usually used in combination with a GROUP BY clause. It is one of the SQL "aggregate" functions, which include AVG (average) and SUM.

The COUNT operator is usually used with the GROUP BY clause. It is one of the SQL "aggregation" functions, including AVG (average) and SUM.

This function will count the number of rows and return that count as a column in the result set.

This function counts the number of rows and returns the count as a column to the result set.

Here are examples of what you would use COUNT for:

The following is an example of using COUNT for the following purposes:

Counting all rows in a table (no group by required)

Calculate all rows in the table (no need to be by group)

Counting the totals of subsets of data (requires a Group By section of the statement)

Calculate the total number of subsets of data (requires the "grouping by" section of the statement)

For reference, here is the current data for all the rows in our example student database.

For reference, this is the current data for all rows in the sample student database.

Select studentID, FullName, programOfStudy, sat_score from student;-- all records with fields of interest

This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using "AS".

This SQL statement provides a count of all rows. Note that you can use "AS" to name the resulting COUNT column.

Select count (*) AS studentCount from student;-count of all records

Here we get a count of students in each field of study.

Here, we get the number of students in each field of study.

Select studentID, FullName, count (*) AS studentCount from the student table with a group by programOfStudy

Here we get a count of students with the same SAT scores.

Here, we get the number of students with the same SAT score.

Select studentID, FullName, count (*) AS studentCount from the student table with a group by sat_score

Here is an example using the campaign funds table. This is a sum total of the dollars in each transaction and the number of contributions for each political party during the 2016 US Presidential Campaign.

This is an example of using an ad campaign funding table. This is the total amount of each transaction and the contribution of each political party during the 2016 US presidential election.

Select Specific_Party,Election_Year, format (sum (Total_$), 2) AS contribution$Total, count (*) AS numberOfContributions from combined_party_datagroup by Specific_Party,Election_Yearhaving Election_Year = 2021

As with all of these things there is much more to it, so please see the manual for your database manager and have fun trying different tests yourself.

There's still a lot to do about all of these things, so it's interesting to refer to the database administrator's manual and try different tests yourself.

Considerations for aggregate functions and grouping operations of sql statements

Group by can group the query results according to each member of a given data column, and finally get a summary table.

Some of the more important constraints of group by:

(1) column names in select sentences and column names in having or where must be grouped columns or column functions. The column function returns a result for each group defined by the group by clause.

(2) it only makes sense to use group by and aggregate functions, such as count,sum,avg, to use the two elements of group by:

(3) the fields that appear after select are either in the aggregate function or in group by.

(4) to filter the results, you can use where before group by or group by before having.

Item (4) may not be applicable according to each database, so it is best not to use it this way and use having honestly

Syntax and Overview of SQL aggregate functions and packet data

(1) the aggregate function refers to the operation of the data on the column and plays a statistical role. The previous functions are the data obtained from the operation of the most line of records, including the previous date function, mathematical function, character function, conversion function, and conditional judgment function.

Common aggregate functions are: count, sum, avg, max, min. These five functions play the role of counting the number of records, summing, calculating the average value, and finding the maximum and minimum value.

The Count:count function counts the number of records for the data of the query. This function does not count the value of the field whose value is NULL, that is, if the field of a query has a null value, the number of NULL values will be subtracted, so that the query condition can not be set on NULL.

If you want to set a query for a null value, you can use the WHERE field IS NULL as a condition.

The summation of the Sum:sum function can only operate on numeric data, and null values are ignored. For example:

Select sum (examination result) as computer total score

From score

Where course number in (select course number from course where course name = "computer")

Avg: find the average, and the parameter must also be a numeric field name or an expression with a numeric result.

Max, min: these two functions take the maximum and minimum values, but they cannot be placed in WHERER or in the field name of the SELECT clause.

Example: select max (x1) from y where max (x2) in (select …) Wrong grammar.

Wrong syntax for Select x1 from y where x2=max (x3).

Select max (x1) from y where x2) in (select max (x2,) …) That's right.

Note: all 5 functions can use distinct to count non-duplicate values:

Select count (distinct (course)) as course quantity from course schedule

Access and mysql cannot place distinct in parameters. Solution: query distinct is saved as a new table INTO statement, and then count is used.

(2) data grouping refers to dividing the data in the data table into many groups according to the different values of the specified fields and using the group by clause to operate.

Group by usually does not query all fields directly and group them. After group by and select, grouping fields and query fields are usually the same. Because select * from y group by x produces errors, it is common to group a field and use the aggregate function to calculate the value of the grouping.

Aggregate function and grouping and set query conditions: you can group a field and set conditions so that the result will be the value that calculates the grouping and satisfies the condition. Example:

Query the values of all boys in each department:

Select department, count (*) as male number from student where gender = 'male' group by college department

Query histogram: using the replicate () function, the grouped data is used as the number of times to repeat a set symbol.

Sort query results: order by statement, ASC, DESC after group by

The combination of Case expressions and group by:

Department of Select, count (case

When gender = 'male' then 1

Else null

End) number of as boys

Count (case

When gender = 'female' then 1

Else null

End) number of as girls

The department of From student group by

The Hanving clause sets the grouping query condition for the grouping group by.

The Having clause is always used in conjunction with the group by clause, depending on grouping, and you can use aggregate functions in having; where can also set conditions, but does not depend on grouped fields, but cannot use aggregate functions.

Select student number, sum (examination result) as examination total score from score group by (examination result) > 400order by examination total score desc

At this point, I believe you have a deeper understanding of "SQL grouping function group by and aggregate function COUNT, MAX, MIN, AVG, SUM". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.