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

Group functions:

-types and syntax

-use AVG, SUM, MIN, MAX, COUNT

-Group functions use the DISTINCT keyword

-NULL value in group function

Grouping function: acts on a set of data and returns a value for a set of data

Group function type

Average AVG

COUNT statistics

MAX maximum

MIN minimum

Total SUM

STDDEV standard deviation

VARIANCE variance

Group function syntax:

Select group_function (column),... From table [where condition] [order by column]

Using the AVG and SUM functions

You can use AVG and SUM functions for numeric data

1. Query the average wage, the maximum wage and the sum of wages of job_id as REP

Select avg (salary), max (salary), min (salary), sum (salary) from employees where job_id like'% REP%'

Using MIN and MAX

You can use MIN and MAX functions for numeric, character, and date types

2. Query the shortest and longest time for entry

Select min (hire_date), max (hire_date) from employees

Use COUNT

1. Count the number of people in a department with a department_id of 50

Select count (*) from employees where department_id = 50

2. If there is a null value, it will not be counted.

Select count (commission_pct) from employees where department_id=80

3. Display the number of departments in the EMPLOYEES table

Select count (distinct department_id) from employees

Group functions ignore null values

1. Count the commission

Select avg (commission_pct) from employees

2. Count all the people in

Select avg (nvl (commission_pct,0)) from employees

Packet data: GROUP BY

You can use the GROUP BY clause to divide the data in a table into groups.

Column aliases cannot be used after group by, and there are restrictions after select.

1. Find out the average salary of each department in EMPLOYEES

Select department_id,avg (salary) from employees group by department_id order by department_id

2. Columns contained in the GROUP BY clause do not have to be included in the SELECT list.

Select sum (salary) from employees group by job_id

3. Make a multi-group breakdown and get the sum of the wages after grouping according to the department and work.

Select department_id,job_id,sum (salary) from employees group by department_id,job_id order by department_id

Illegal use of group functions

Columns or expressions in the SELECT list, which are not included in the group function, must be included in the GROUP BY clause

Error:

Select department_id, count (last_name) from employees

Or

Select department_id, job_id, count (last_name) from employees group by department_id

In other words, department_id and job_id must be added to group by.

Correct:

Select department_id, count (last_name) from employees group by department_id

Or

Select department_id,job_id, count (last_name) from employees group by department_id,job_id

You cannot use the WHERE clause to filter a group

You can use the HAVING clause to filter groups

Error:

Select department_id, avg (salary) from employees where avg (salary) > 8000 group by department_id

Filter grouping: HAVING clause

Use the HAVING clause to filter grouping conditions:

Rows have been grouped.

Group functions are used.

Groups that meet the conditions in the HAVING clause will be displayed

Syntax:

Select column, group_function from table [where condition]

[group by group_by_expression]

[having group_condition]

[order by column]

1. The maximum salary of each department is more than $10000

Select department_id,max (salary) from employees group by department_id having max (salary) > 10000

two。 Look for jobs that do not have a total salary of more than 13000 for REP, and sort by sum salary.

Select job_id, sum (salary) from employees where job_id not like'% REP%' group by job_id having sum (salary) > 13000

Order by sum (salary)

Nested group function

Show the maximum average wage by department:

Select max (avg (salary)) from employees group by department_id

But nested group functions don't seem to be able to add new columns.

Exercise:

1. Find out the maximum, minimum and average wages of all employees. And modify the aliases of each column.

For "Maximum", "Minimum", "Sum", "Average". And the results are required to be rounded.

Select round (max (salary), 0) "Maxinmum", round (min (salary), 0) Minimum, round (sum (salary), 0) Sum, round (avg (salary), 0) Average from employees

2. Group by job_id to view the maximum, minimum, sum, and average of wages for each type of job

Select job_id,max (salary), min (salary), sum (salary), avg (salary) from employees group by job_id

3. Write a query statement to count the number of employees for each type of job

Select job_id,count (employee_id) from employees group by job_id

4. Let colleagues in the HR department enter a job, and then SQL returns the number of employees for that job.

Select job_id,count (*) from employees where job_id like'& job_title' group by job_id

5. Directly show the total number of all managers. And mark the column as Number of Managers. Tip: use the MANAGER_ID column to determine the number of managers

Select count (distinct manager_id) "Number of Managers" from employees

6. Find out the difference between the highest salary and the lowest salary and mark the column as DIFFERENCE

Select max (salary)-min (salary) "DIFFERENCE" from employees

7. please find out the lowest-paid employees of each manager, those who do not have a manager need to be excluded, and those who need to exclude the minimum salary

Less than or equal to 6000 groups. Finally, the structure is arranged in descending order according to salary.

Select manager_id,min (salary)

From employees

Where manager_id is not null

Group by manager_id

Having min (salary) > 6000

Order by min (salary) desc

8. Please write a SQL statement to check the total number of employees and the number of employees employed in 1996, 1997, 1998 and 1999, and give each column an appropriate alias.

Select count (*) total

Sum (decode (to_char (hire_date,'fm YYYY'), 1999)) "1999"

Sum (decode (to_char (hire_date,'fm YYYY'), 1998)) "1998"

Sum (decode (to_char (hire_date,'fm YYYY'), 1997)) "1997"

Sum (decode (to_char (hire_date,'fm YYYY'), 1996)) "1996"

From employees

9. Please show the desired results through a matrix, and the requirements are to calculate the corresponding types of work according to the department number (20p50pd80).

Capital, as well as the sum of wages for this type of job, for the columns whose department number is 20re50, 80pr 90, please give an appropriate alias.

Select job_id "job"

Sum (decode (department_id,20,salary)) "dept 20"

Sum (decode (department_id,50,salary)) "dept 50"

Sum (decode (department_id,80,salary)) "dept 80"

Sum (decode (department_id,90,salary)) "dept 90"

Sum (salary) "total"

From employees

Group by job_id

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.