Guide to Oracle SQL (University at Buffalo 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
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
The COUNT()
function is used to return the number of occurances of a field in the table.
Function | Description |
---|---|
COUNT(*) | Returns a count of all the rows in the result set including NULL and duplicate values. |
COUNT(DISTINCT expr) | Returns a count of all the rows in the result set without conting NULL or duplicate values. |
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 COUNT(ALL expr) . |
COUNT()
FunctionThe AVG()
, SUM()
, MAX()
, MIN()
, STDDEV()
, and VARIANCE()
functions use the same parameters as the COUNT()
function. As I mentioned in a previous chapter, you can also nest these functions.
The COLUMN
Command
This may be a good point to introduce the COLUMN command to FORMAT
the 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.
The IN
And OR
Conditionals
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.
The 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".
The HAVING
Clause
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.