Databases Reference
In-Depth Information
described earlier in this chapter can be applied to these groups. Note the following
rules about using the GROUP BY clause:
◆
All columns in a SELECT statement that are not in the GROUP BY clause
must be part of an aggregate function.
The WHERE clause can be used to filter rows from the result before the
grouping functions are applied.
◆
The GROUP BY clause also specifies the sort order; this can be overridden
with an ORDER BY clause.
◆
Column aliases cannot be used in the GROUP BY clause.
Janice has been busy preparing a report for King that will break down the sal-
ary and commission information by department. Her first query looks like this:
◆
select department_id "Dept", count(*), avg(salary),
avg(commission_pct) from employees
group by department_id;
Dept COUNT(*) AVG(SALARY) AVG(COMMISSION_PCT)
----- ---------- ----------- -------------------
10 1 4400
20 2 9500
30 6 4150
40 1 6500
50 45 3475.55556
60 5 5760
70 1 10000
80 34 8955.88235 .225
90 3 19333.3333
100 6 8600
110 2 10150
1 7000 .15
12 rows selected.
This gives King a breakdown, by department, of the employee count, the average
salary, and the average commission. NULLs are not included in the calculation for
commission or salary. King likes this report, but Janice suspects that he will be asking
for something different tomorrow.
One of the departments has a NULL value. There is one employee who has not
yet been assigned to a department, but this employee does have a salary and a
commission.
