Databases Reference
In-Depth Information
Notice that the average commission in this case is not zero but NULL; there
were
no
employees in department 90 with a commission. The result would have
been non
-
NULL, if there were at least one employee who worked on a commission
for part of their salary.
The next morning, the boss asks the same question for department 80, which has
the bulk of the commissioned employees. Janice gets the answer with this query:
select count(*), avg(salary),
avg(commission_pct) from employees
where department_id = 80;
COUNT(*) AVG(SALARY) AVG(COMMISSION_PCT)
---------- ----------- -------------------
34 8955.88235 .225
1 row selected.
Janice hears rumors that King is going to ask for a breakdown of the number
of employees, how many are on commission, and how many distinct commission
percentages there are. She comes up with this query:
select count(*), count(commission_pct) "Comm Count",
count(distinct commission_pct) "Distinct Comm"
from employees;
COUNT(*) Comm Count Distinct Comm
---------- ---------- -------------
107 35 7
1 row selected.
What does this tell King? The total number of employees is 107, regardless of
whether there are any NULL values in any of the columns. Of those employees, 35
are on commission (have a non-NULL value for COMMISSION_PCT), and out of
those 35, there are seven different commission levels in force at the company.
Janice also suspects that King will be asking for some statistics for other
departments. Rather than run the same query for different department numbers,
she decides that it might be worthwhile to use the GROUP BY function to give King
all the information he needs in a single query.
The
GROUP BY
Clause
The GROUP BY clause is used to break down the results of a query based on a column
or columns. Once the rows are subdivided into groups, the aggregate functions
