[SQL Entry] Next, continue querying

Distance from the previous time is quite long, this article continues to query, a simple summary of the aggregation function, grouping knowledge.

One, aggregate function (group function/multiline function)

What is a multi-line function, as the name implies, is a function of multiple rows of data to produce an output, what does it mean? Look at the picture:

So what are the usual functions?

AVG(average)

sum(total)

count (count)

Max(max)

min(minimum value)

STDDEV(standard deviation)

The syntax used by group functions is simple, but there are two points to note:

I. Function parameter types

1. Using the AVG and SUM functions for numeric data
2. Use the MIN,MAX, COUNT functions for data of any data type

II. handling of NULL values

1. The first group function ignores null values, for example, COUNT (*) returns the number of records in the table, and count (expr) returns the number of records in which the expr field is not empty
2. So how to deal with the null value, you need to use the NVL function mentioned in the previous article,SELECT AVG (NVL (commission_pct, 0)) from employees
3. DISTINCT keyword expression non-null and not duplicated , count (DISTINCT expr) returns the number of records that are not empty and are not duplicates

III. You cannot use group functions in the WHERE clause, which is explained by the HAVING clause that follows.

Iv. group functions can be nested using

Second, data grouping

GROUP BY clause, grouped data

I will not talk nonsense, use the time to pay attention to two points:

• In all columns following the SELECT, columns that do not use an aggregate function must appear after group by, and the columns that are included in the GROUP BY clause are not necessarily included in the select list.
• The GROUP by clause can contain more than one column, and the consent also applies to rule 1.

A little dizzy, what do you mean? Let me explain, judging whether an SQL statement is legal or not, you can actually try to understand the actual idea of the SQL statement you want to write, such as:

• Select department_id,job_id,avg (salary)
  From Employees
  GROUP BY department_id,job_id

As the above statement, I would like to express the meaning of: from the employee this table to find out the average salary of different departments and different types of people, the output format is according to the SELECT statement list. So if I delete a field from the select list, I'm just changing the format of my output, and I'm classifying it by department and job type, but if only from group

By clause in the deletion of job_id, then the expression of the meaning of the statement can not be set up, I grouped by department, but there are many types of jobs in the department, the results of how to correspond is unclear.

Having clauses, filtering groups

• The purpose of the WHERE clause is to filter the rows of data that satisfy the condition, i.e. filter data before grouping, you cannot use aggregate functions in conditions, and use where conditions to display specific rows;
• the HAVING clause is the function of filtering a group that satisfies a condition, i.e. filter groups after grouping, which often contains aggregate functions, and displays specific groupings using the having condition;

[SQL Entry] Next, continue querying