Guide to Oracle SQL (Buffalo State University 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 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
COUNT() function is used to return the number of occurances of a field in the table.
|COUNT(*)||Returns a count of all the rows in the result set including |
|COUNT(DISTINCT expr)||Returns a count of all the rows in the result set without conting |
|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 |
VARIANCE() functions use the same parameters as the
COUNT() function. As I mentioned in a previous chapter, you can also nest these functions.
The This may be a good point to introduce the COLUMN command to
FORMATthe 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.
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.
GROUP BY Clause
You can use the
GROUP BY clause to present the result set with similiar data treated as a "group".
You can use
GROUP BY to present the result set with similiar data treated in more than one "group".
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.