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)`.

Figure 6-1: The COUNT() Function

The 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

Figure 6-2: The SUM Function

The `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.

Figure 6-3: The AVG() Function

The `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

Figure 6-4: The OR and IN Operators

The `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

Figure 6-5: The GROUP BY Clause

You 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

Figure 6-6: Using the GROUP BY Clause To Group Multiple Columns

The `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

Figure 6-7: The HAVING Clause

Guide to Oracle SQL (University at Buffalo Version)

Reporting Aggregated Data Using the Group Functions

Group Functions

SQL Aggregate Functions

The COLUMN Command

The IN And OR Conditionals

The GROUP BY Clause

The HAVING Clause

The `COLUMN` Command

The `IN` And `OR` Conditionals

The `GROUP BY` Clause

The `HAVING` Clause