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

This article mainly introduces the method of how to report summary data and use group functions in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let's take a look at it.

First, grammar

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column]

2. Group function

The grouping function can operate on a rowset and give a result for each group. These collections can be entire tables or groups divided by tables.

Function content AVG syntax: AVG ([DISTINCT | ALL] n) description: the average value of n, ignoring null value example sentence:

SELECT AVG (SALARY) FROM EMPLOYEES

COUNT syntax: COUNT ({* | [DISTINCT | ALL] expr}) description: number of rows, where expr is used to determine non-null values (use * to calculate all selected rows, including duplicate rows and rows with null values) example sentence:

SELECT COUNT (*) FROM EMPLOYEES

MAX syntax: MAX ([DISTINCT | ALL] expr) description: the maximum value of expr, ignoring null values for example sentences:

SELECT MAX (SALARY) FROM EMPLOYEES

MIN syntax: MIN ([DISTINCT | ALL] expr) description: the minimum value of expr, ignoring null value example sentence:

SELECT MIN (SALARY) FROM EMPLOYEES

SUM syntax: SUM ([DISTINCT | ALL] n) description: the total value of n, ignore null value example sentence:

SELECT SUM (SALARY) FROM EMPLOYEES

III. Grouping

1 、 GROUP BY

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

A) all columns in the SELECT list that are not included in the group function should be included in the GROUP BY clause.

B) columns contained in the GROUP BY clause do not have to be included in the SELECT list.

2 、 HAVING

You cannot use the WHERE clause to filter a group

You can use the HAVING clause to filter groups

3. Example sentences

-- GROUP BY

-- the highest, lowest and average wages in each department

SELECT DEPTNO, MAX (SAL), MIN (SAL), AVG (SAL)

FROM EMP

GROUP BY DEPTNO

-- HAVING

Which employees have more than 3 direct subordinates, list the employee name and the number of direct subordinates

SELECT M.ENAME, COUNT (*)

FROM EMP W, EMP M

WHERE W.MGR = M.EMPNO

GROUP BY M.ENAME

HAVING COUNT (*) > 3

4. Nested group functions

Example:

SELECT MAX (AVG (salary))

FROM employees

GROUP BY department_id

Thank you for reading this article carefully. I hope the article "how to report summary data using group functions in the database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you 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.