Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to use group by in the database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

one。 Overview

Group_by means to group data according to which fields or which fields are grouped according to by.

two。 Grammar

Select field from table name where condition group by field

Or

Select field from table name group by field having filter condition

Note: for filter conditions, you can use where first, then group by, or group by first, then having

three。 Case 1 creates a table and inserts data

Description: create a table on plsql developer and insert data for simple field grouping and multiple field grouping, as well as operations combined with aggregate functions.

Create a student table

Create table student

(id int not null

Name varchar2 (30)

Grade varchar2 (30)

Salary varchar2 (30)

)

Insert data into the student table

Insert into student values (1 century Zhangsanqie 1 500)

Insert into student values (2, recording, etc.)

Insert into student values (1 century Zhangsanqie 1 500)

Insert into student values (4 people Qianwuqianwuqiang praises 3500)

Insert into student values (3 recordings zhaoliujia page2 000)

Insert into student values (1 recordings huyifeifang pr é cor dudes 2500)

The result of inserting data into the student table

2 single field grouping

① select grade from student finds all student levels (including duplicate levels)

② select grade from student group by grade finds out the types of student grades (by grade, excluding duplicates)

3 grouping multiple fields

Select name, sum (salary) from student group by name, grade are divided by name and rank, and view the total salary under the same name.

Note: it is important to note that when grouping multiple fields, you need to treat name and grade as a whole. As long as name and grade are the same, they can be divided into a group; if only the name is the same, the grade is not the same group.

4 use it with aggregate function

Common aggregate functions: count (), sum (), avg (), max (), min ()

Count (): count

Select name, count (*) from student group by name to view the number of people with the same name in the table

The results are as follows

Sum (): summation

Select name, sum (salary) from student group by name check the salary and (the salary of the same surname is added) in the table.

The results are as follows

Avg (): average

Select name, avg (salary) from student group by name, grade view the average salary of the staff in the table (average salary of the same surname)

The results are as follows

Max (): maximum

Select grade, max (salary) from student group by grade to view the maximum salary of personnel by grade

The results are as follows

Min (): minimum

Select grade, min (salary) from student group by grade to view the minimum salary of personnel by grade

The results are as follows

This is the end of the article on "how to use group by in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.