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

This article introduces the relevant knowledge of "how to divide mysql into groups for summation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In mysql, you can use the select statement with the SUM () function and "GROUP BY" to group the sum. The syntax is "SELECT query field SUM (sum field) AS field FROM table name WHERE condition GROUP BY grouping field one, grouping field two".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to divide mysql into groups and sum

In mysql, you can take advantage of

SELECT query field SUM (summation field) AS field FROM table name WHERE condition GROUP BY grouping field one, grouping field two

To sum up in groups.

Examples are as follows:

1) count () to find the number of rows in a column

It is easy to understand that count (column name) is to count the number of rows in a column, and it is worth noting that the column does not count the number of rows with null values. For example:

>-- query how many teachers there are

>-- query the number of rows in the teacher table SELECT COUNT (*) FROM teacher

What if there is a duplicate value in the column and we do not want to calculate the duplicate value? We can use distinct to solve the problem:

-- query the scores of several students in the transcript: SELECT COUNT (DISTINCT student number) FROM score

2) sum () sums a column of data

Summation is the summation of the values of a column, which can only be calculated. For example:

-- the sum of all grades SELECT SUM (grades) FROM score

3) avg () averages a column of values

Similarly, the calculation of averages can only be performed on numerical values:

-averaging all grades SELECT AVG (grades) FROM score

4) max () finds the maximum of a column of data, and min () calculates the minimum of a column of data.

-- get the highest score of all grades, SELECT MAX (grade), MIN (grade) FROM score

Grouping

In the question, we mentioned that we need to know the average grade point of each subject, so we need to group into groups.

In SQL, group by statements can group result sets according to one or more columns. For example:

-- calculate the average grade point of each course SELECT course number, AVG (grade) FROM score GROUP BY course number

In the previous article, we briefly introduced the execution order of SQL statements: which table to get data from by executing from first, and then executing select statements

Here we get the data from the score table, use the group by statement to group the data according to some rules, then calculate count () on the results of the grouping, and finally select the results, summarizing each group of results in the previous step into a table.

Specify conditions for grouping conditions

At the beginning of the question, it is required to "calculate the average score of each subject and get that the average score is greater than or equal to 80 points". In the above two sections, we have calculated the average grade point of one subject every day, and now specify the condition for the average score: "greater than or equal to 80 points". We use the having clause.

Both the having clause and the where clause are conditional selection data, except that where cannot be used with the summary function.

-calculate courses with an average score of 80 or greater; SELECT course number, AVG (grade) FROM score GROUP BY course number HAVING AVG (grade) > = 80

Does that mean that the order of having clauses is also before the summary of select results? Yes, the having clause specifies the conditions for the grouping results of group by, such as requiring 80 points or more. After the results are screened, the results are summarized.

-- sort the teacher table by teacher name SELECT * FROM teacher ORDER BY teacher name

We found that the null value Null is at the top of the list, so: when the column with null value is sorted, the null value null will be at the beginning, when the amount of data is large and you want to see the null value, you can use this sort.

In addition, add a limit statement to extract the specified row from the query result, for example, if we only take the first row from the query result just now:

-- calculate the courses with an average score of 80 or more and rank them in descending order according to the grade, and get the first line SELECT course number, AVG (grade) FROM score GROUP BY course number HAVING AVG (grade) > = 80 ORDER BY AVG (grade) DESC LIMIT 1

Next, let's make a summary.

Analysis idea of solving Business problems with SQL

To put it simply: clarify the problem, translate the problem into vernacular, disassemble it step by step, and write out the analytical ideas and the corresponding SQL ideas.

1) identify the question and translate it into vernacular:

Calculate the average score of each subject and get the courses with an average score greater than or equal to 80 and arrange them in descending order.

Calculate the average score of each course, and then according to the results, make a conditional query of more than or equal to 80 points, and arrange the query results in descending order.

2) disassemble it step by step, and write out the analysis ideas and the corresponding SQL ideas:

① groups course numbers and calculates the average grade point of each course.

② specifies the condition "> = 80" for the result of ①

③ sorts the results of ② in descending order

We can apply the disassembled idea to the following formula:

Select query results from looks up data from that table where query conditions (operator, fuzzy query) group by grouping (each) having specifies conditions for grouping results order by sorts query results limit fetches specified rows from the query results

Give it a try:

Select query results [course number, avg] from looks up data from that table [score sheet score] where query conditions (operator, fuzzy query) [No] group by grouping (each) [course number] having assigns conditions to grouping results [avg (grades) > = 80] order by sorts query results [avg (grades) desc] limit fetches specified rows from the query results [No] this is the end of the introduction of "how to divide mysql into groups for summation". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.