SQL/MX Quick Start (G06.24+, H06.03+)

Displaying Information About Groups of Rows
HP NonStop SQL/MX Quick Start—523724-002
4-5
Computing Averages for Groups
Tip
The keyword DISTINCT causes each unique customer number for a particular sales
representative to be counted only once. Sales representative 226 has three orders, but
two of the orders are from the same customer.
Computing Averages for Groups
For some queries, you might want to get information about one or two specific groups.
The next example uses the ODETAIL table to illustrate this technique.
Example
Display part numbers for which the average quantity ordered is greater than 100.
Specify the conditions for selecting the groups in the HAVING clause:
SELECT PARTNUM, AVG(QTY_ORDERED)
FROM ODETAIL
GROUP BY PARTNUM
HAVING AVG(QTY_ORDERED) > 100;
The selected row is:
Part/Num (EXPR)
-------- ---------------
4102 130
--- 1 row(s) selected.
Tip
A HAVING clause is similar to a WHERE clause, but the HAVING clause is applied to
the result of the GROUP BY clause. A column that you specify in a condition of the
HAVING clause is typically a grouping column.
To include a column that is not a grouping column, use the column as an argument of
an aggregate function. In the preceding example, QTY_ORDERED is not a grouping
column, but you can include it in the HAVING clause because it is the argument of the
AVG function.