Java Reference
In-Depth Information
SELECT DESCRIPTION, COUNT(DESCRIPTION) AS 'COUNT', AVG(COST)
AS 'AVERAGE COST', MIN(COST) AS 'LOWEST COST', MAX(COST)
AS 'HIGHEST COST'
FROM INVENTORY
GROUP BY DESCRIPTION;
This query generates the following results:
Description
Count
Average Cost
Lowest Cost
Highest Cost
Cereal
4
1.745
0.98
2.05
Cookies
3
1.2866
1.03
1.57
Soda
3
0.63
0.57
0.71
The fundamental difference between aggregate functions and standard
functions is that the former use the entire column of data as their input and
produce a single output.
Note
Using the HAVING Clause to Filter Groups
There are going to be situations where you'll want to filter the groups themselves in
much the same way as you filter records using the WHERE clause. For exa mple, you
may want to analyze your sales by state, but ignore states with a limited number of
customers.
To filter groups, apply a HAVING clause after the GROUP BY clause. The HAVING
clause lets you apply a qualifying condition to groups so that the RDBMS returns a
result only for the groups that satisfy the condition.
HAVING clauses can contain one or more predicates connected by ANDs and ORs.
Each predicate compares a property of the group (such as COUNT(State)) with either
another property of the group or a constant.
The following example shows the use of the HAVING clause to compute a count of
customers by state, filtering out results from states with only one customer:
SELECT DESCRIPTION, STATE, COUNT(STATE) AS 'COUNT',
SUM(oi.QTY * i.COST) AS TOTAL
FROM CUSTOMERS c, ORDERS o, ORDERED_ITEMS oi,
INVENTORY i
WHERE c.CUSTOMER_NUMBER = o.CUSTOMER_NUMBER AND
o.ORDER_NUMBER = oi.ORDER_NUMBER AND
i.ITEM_NUMBER = oi.ITEM_NUMBER
GROUP BY STATE, DESCRIPTION
HAVING COUNT(STATE) > 1;
