Lesson 5	Group operators
Objective	Describe group operators and what they do.

SQL Group Operators

Group functions

When you combine rows of information together and display summarized data, you are performing group functions on the data. The table below shows you the group functions that can be used in Oracle:

Group function	Description
`SUM()`	Adds up a total amount for the column or expression within the parentheses
`MAX()`	Finds the maximum value for the column or expression within the parentheses
`MIN()`	Finds the minimum value of the column or expression within the parentheses
`AVG()`	Finds the average value (ignoring null values)
`COUNT()`	Finds the number of rows in which the column being counted is not null
`STDDEV()`	Calculates the standard deviation of values
`VARIANCE()`	Calculates the variance of values

Here is a typical query that adds up sales by customer:

SELECT FIRSTNAME, LASTNAME, SUM(TOTAL_SALE_AMOUNT)
FROM CUSTOMER C, CUSTOMER_SALE CS
WHERE C.CUST_ID = CS.CUST_ID
GROUP BY FIRSTNAME, LASTNAME
ORDER BY FIRSTNAME, LASTNAME

The GROUP BY clause determines how the SUM function groups the query rows when it adds them up. In the example, each uniquecombination of FIRSTNAME and LASTNAME has a summary of the TOTAL_SALES_AMOUNT column. While GROUP BY is not required, it is a common ingredient in queries that use group functions. ^[1]

Whenever you use the GROUP BY clause, add a corresponding ORDER BY clause so that all rows are evaluated and grouped properly.

DISTINCT in Group Functions

All group-value functions have a DISTINCT versus ALL option. COUNT provides a good example of how this works. Here is the format for COUNT (note that | means or):

COUNT([DISTINCT | ALL] value)

Here is an example:

select COUNT(DISTINCT City), COUNT(City), COUNT(*)
from COMFORT;
COUNT(DISTINCTCITY) COUNT(CITY) COUNT(*)
------------------- ----------- --------
2                   8           8

This query shows a couple of interesting results. First, DISTINCT forces COUNT to count only the number of unique city names. If asked to count the DISTINCT midnight temperatures, it would return 7, because two of the eight temperatures were the same.
When COUNT is used on City but not forced to look at DISTINCT cities, it finds 8. This also shows that COUNT can work on a character column. It is not making a computation on the values in the column, as SUM or AVG must; it is merely counting how many rows have a value in the specified column. COUNT has another unique property: value can be an asterisk, meaning that COUNT tells you how many rows are in the table, regardless of whether any specific columns are NULL. It will count a row even if all its fields are NULL.
The other group functions do not share COUNT's ability to use an asterisk, nor its ability to use a character column for value (although MAX and MIN can). They do all share its use of DISTINCT, which forces each of them to operate only on unique values.
The next lesson describes three group operators you can use when working with groups of data.

[1] Group function: A group function is a predefined way to combine rows of information together and display summarized data. A common group function is the SUM function, which adds values into a total sum.