Guide to Oracle SQL (University at Buffalo Version)
Reporting Aggregated Data Using the Group Functions
In previous chapters I showed example of single row functions. This chapter I will introduce the concept of group functions.
Group Functions
Group functions deal with multiple rows of aggregated data in a result set. Using the GROUP BY
clause provides a way to present like data, such as all the employees grouped by department.
SQL Aggregate Functions
The COUNT()
function is used to return the number of occurances of a field in the table.
Function | Description |
---|---|
COUNT(*) | Returns a count of all the rows in the result set including NULL and duplicate values. |
COUNT(DISTINCT expr) | Returns a count of all the rows in the result set without conting NULL or duplicate values. |
COUNT(ALL expr) | Returns the count of all the rows in the result set that match the expression This is the default. |
COUNT(expr) | Same as COUNT(ALL expr) . |
COUNT()
FunctionThe AVG()
, SUM()
, MAX()
, MIN()
, STDDEV()
, and VARIANCE()
functions use the same parameters as the COUNT()
function. As I mentioned in a previous chapter, you can also nest these functions.
SELECT TO_CHAR(SUM(salary),'$9,999,999') AS "Total Salary" , department_id FROM employees GROUP BY department_id ORDER BY department_id; Total Salar DEPARTMENT_ID ----------- ------------- $55,000 20 $25,000 50 $90,000 60 $930,000 80 $67,000 90
SUM
FunctionThe COLUMN
Command
This may be a good point to introduce the COLUMN command to FORMAT
the column headers and column width of your result set output. The next figure illustrates how to make the "Years Worked" column wide enough to display the entire heading text. I will go into more deatil about formatting your output reports in a later chapter.
COLUMN "Years Worked" FORMAT A15;COLUMN "Years Worked" FORMAT A15; SELECT last_name, job_id, TO_CHAR((SYSDATE-hire_date)/375.25,'99') AS "Years Worked" , department_id FROM employees; LAST_NAME JOB_ID Years Worked ------------------------- ---------- --------------- Oates SA_MAN 11 Hall IT_PROG 12 Hall SA_MAN 11 Felix IT_PROG 12 Jones MK_REP 8 Lindgren ST_CLERK 7 Scott AD_ASST 10 Wertz SA_MAN 31 Taylor AD_ASST 12 Taylor SA_MAN 21 10 rows selected.
AVG()
FunctionThe IN
And OR
Conditionals
In a previous chapter I mentioned the OR
operator that you could use on a WHERE
condition. The IN
operator performs a similiar operation. This is useful if you have many options that might be matched.
SELECT last_name, job_id FROM employees WHERE job_id = 'ST_CLERK' OR job_id = 'MK_REP'; LAST_NAME JOB_ID ------------------------- ---------- Jones MK_REP Lindgren ST_CLERK SELECT last_name, job_id FROM employees WHERE job_id IN ('ST_CLERK', 'MK_REP'); LAST_NAME JOB_ID ------------------------- ---------- Jones MK_REP Lindgren ST_CLERK
OR
and IN
OperatorsThe GROUP BY
Clause
You can use the GROUP BY
clause to present the result set with similiar data treated as a "group".
SELECT COUNT(*), department_id FROM employees GROUP BY department_id ORDER BY department_id; COUNT(*) DEPARTMENT_ID ---------- ------------- 1 20 1 50 2 60 4 80 2 90
GROUP BY
ClauseYou can use GROUP BY
to present the result set with similiar data treated in more than one "group".
COLUMN "Average Salary" FORMAT A14 SELECT job_id, department_id, TO_CHAR(AVG(salary), '$9,999,999') AS "Average Salary" FROM employees GROUP BY department_id, job_id; JOB_ID DEPARTMENT_ID Average Salary ---------- ------------- -------------- ST_CLERK 50 $25,000 AD_ASST 90 $33,500 SA_MAN 80 $232,500 IT_PROG 60 $45,000 MK_REP 20 $55,000
GROUP BY
Clause To Group Multiple ColumnsThe HAVING
Clause
You can use the HAVING
clause on a GROUP BY
statement much the same way as you would use the WHERE
clause. The WHERE
close places a condition (restriction) on the SELECT
statement while the HAVING
claus places a condition (restriction) on the GROUP BY
clause.
SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING department_id = '80' ORDER BY department_id; JOB_ID DEPARTMENT_ID Average Salary ---------- ------------- -------------- SA_MAN 80 $232,500
HAVING
Clause