Group By and aggregation function can be used to achieve Group accumulation. For example, if you were asked to display the salary totals for each
department, you could use the following statement.
SELECT department_number
,SUM (salary_amount)
FROM employee
GROUP BY department_number;
result:
department_number Sum(salary_amount)
401 74150.00
403 80900.00
301 58700.00
note that all the fields accumulated without grouping in the SELECT clause must appear in the
GROUP BY clause, otherwise the following error message will be returned:
ERROR: 3504 Selected non-aggregate values must be part of the associated group.
for the above example the Department_Number field is not accumulated so it must appear in the group
BY clause. This basic rule must be borne in mind.
WHERE clause and GROUP BY clause
WHERE clause and GROUP BY clause
WHERE clause and GROUP BY clause are used together, GROUP BY only performs grouping aggregation calculation on data records that conform to the WHERE limit
system. In other words, the WHERE sub-
sentence removes unqualified data records before doing the actual aggregate calculation. What’s the combined salary for department 401 and 403?
SELECT department_number
,SUM (salary_amount)
FROM employee
WHERE department_number IN (401, 403)
GROUP BY department_number
;
results:
department_number Sum (salary_amount)
403 80900.00
401 74150.00
GROUP BY and ORDER BY
after the GROUP BY, ORDER BY makes the grouping statistics show
in the specified ORDER. For example, to show the number of people in a department, the total salary, the highest salary in a department, the lowest salary in a department
, and the average salary in a department by department number, you can use the following SQL statement:
SELECT department_number (TITLE ‘DEPT’)
,COUNT (*) (TITLE ‘#_EMPS’)
,SUM (salary_amount) (TITLE ‘TOTAL’)
(FORMAT ‘zz, ZZZ,zz9.99’)
,MAX (salary_amount) (TITLE ‘HIGHEST’)
(FORMAT ‘ ‘zz, ZZZ,zz9.99’)
,MIN (TITLE ‘amount)
(FORMAT ‘zz, ZZZ,zz9.99’)
,AVG (TITLE ‘AVERAGE’)
(FORMAT ‘zz, ZZZ,zz9.99’)
FROM employee
GROUP BY department_number
ORDER BY department_number
;
results are as follows:
DEPT #_EMPS TOTAL LOWEST AVERAGE
301 3 116,400.00 57,700.00 29,250.00 38,800.00
401 7 245,575.00 46,000.00 24,500.00 35,082.14
403 6 233,000.00 49,700.00 31,000.00 38,833.33
SELECT department_number AS DEPT
,COUNT (*) AS #_EMPS
,CAST (SUM (salary_amount) AS FORMAT ‘zz, ZZZ,zz9.99’)
AS TOTAL
,CAST (MAX (salary_amount) AS FORMAT ‘zz, ZZZ,zz9.99’)
AS LOWEST
,CAST (MIN (salary_amount) AS FORMAT ‘zz, ZZZ,zz9.99’)
AS br>,CAST (AVG (salary_amount) AS FORMAT ‘zz, ZZZ,zz9.99’)
AS _AVERAGE)
FROM employee
GROUP BY department_number
ORDER BY department_number; Since AVERAGE is itself a keyword, in the above example, it is preceded by an underscore
to distinguish it.
when grouping statistics on multiple fields, GROUP BY produces only one level of summary. Example
such as: for the department 401 and 403 according to the work code group statistics salaries.
SELECT department_number
,job_code
,SUM (salary_amount)
FROM employee
WHERE department_number IN (401, 403)
GROUP BY department_number, job_code
ORDER BY 1, 2;
results:
department_number job_code SUM (salary_amount)
401 411100 37850.00
401 412101 107825.00
401 412102 56800.00
401 413201 43100.00
403 431100 31200.00
403 432101 As you can see from this example, when there are multiple fields in GROUP BY, it can only produce a summary of level
, and the summary is made according to the last field (here is job_code).
GROUP BY and HAVING conditions
HAVING conditions clause is used together with GROUP to limit the result of grouping statistics to
and only return the grouping statistics that meet its conditions.
for example, show the number of people in the department, total salary, maximum salary in the department,
minimum salary and average salary in the department by department number order, if only show the department with average salary less than 36000.
SELECT department_number (TITLE ‘DEPT’)
,COUNT (*) (TITLE ‘#_EMPS’)
,SUM (salary_amount) (TITLE ‘TOTAL’)
(FORMAT ‘zz, ZZZ,zz9.99’)
,MAX (salary_amount) (TITLE ‘HIGHEST’)
(FORMAT ‘ ‘zz, ZZZ,zz9.99’)
,MIN (TITLE ‘amount)
(FORMAT ‘zz, ZZZ,zz9.99)
,AVG (TITLE ‘AVERAGE’)
(FORMAT ‘zz, ZZZ,zz9.99’)
FROM employee
GROUP BY department_number
HAVING AVG (salary_amount) < 36000;
result:
DEPT #_EMPS TOTAL b> 401 7 245,575.00 46,000.00 24,500.00 35,082.14
1, WHERE: data records are used to qualify the tables that participate in the grouping aggregation operation. Only
data records that meet the criteria are selected to participate in the grouping aggregation.
2, GROUP BY: to GROUP the records that meet the WHERE clause
3, HAVING: to qualify the GROUP aggregation results that can be returned
4, ORDER BY: to specify the output ORDER of the results