Group functions speaker notes
Previous presentations have covered row functions. This presentation will cover group functions.
Group functions produce results based on a group or set of rows.
The only group function that deals with null values is COUNT(*). Other functions can be forced to deal with null values by using the NVL function.
Null values are ignored by all group functions except COUNT(*).
NVL tests a field/column for a null value and inserts a temporary value in that column for display and calculation purposes. Note that it does not change the data in the table.
To make a column that contains a null value get included in the SUM, the NVL function must be used.
AVG divides the total by the count to get the average amount.
Note: In the third example what I did in the SUM function has no impact on the AVG function. It is the 1000 that replaced the null value in the AVG function that yielded the 1500.
Note, replacing a null field with 0 has no impact on the MAX when you are dealing with positive numbers.
The GROUP BY clause allows the developer to break the table down into essentially subgroups that will be evaluated by the group function.
Note, the only column/field names that can be used in the select are the column/fields that you are in the GROUP BY clause.
A WHERE clause can be used to exclude things from the groups
Grouping is done by ascending order on the column that is being used for the grouping. To change this, you can use the ORDER BY clause.
In this case, I am excluding anyone that has a jobcode = 'IN'.
NOTE the use of != to mean not equal to.
GROUP BY with multiple things means the first thing listed is the major grouping and the next thing is a sub grouping.
GROUP BY is not limited to two groups. Clearly its value lies in processing data in a larger table.
NOTE that when there is only one record per group, the minimum and maximum are the same.
MIN and MAX of the AVG shown above is going to return one item for each and they will have different jobcodes. In addition it will not be returning a line for each group. Because of this, you are not allowed to include the jobcode in the SELECT.
WHERE clause versus HAVING clause
My understanding is that if you restrict prior to forming the groups you use the WHERE clause. If you restrict after the actual groups have been formed, then you must use the where clause.
WHERE clause is used prior to forming groups.
HAVING clause is used to test the groups after they have been formed.