SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—544517-008
7-14
Examples of SAMPLE
For this query execution, the number of rows returned is limited by the total
number of rows in the SALESPERSON table. Therefore, it is possible that no rows
are returned, and the result is null.
•
This query illustrates sampling after execution of the WHERE clause has chosen
the qualifying rows. The query computes the average salary of a random 10
percent of the sales people over 35 years of age. You will get a different result
each time you run this query because it is based on a random sample.
SELECT AVG(salary)
FROM salesperson
WHERE age > 35
SAMPLE RANDOM 10 PERCENT;
(EXPR)
--------------------
58000.00
--- 1 row(s) selected.
•
Compute the average salary of a random 10 percent of sales people belonging to
the CORPORATE department. The sample is taken from the join of the
SALESPERSON and DEPARTMENT tables. You will get a different result each
time you run this query because it is based on a random sample.
SELECT AVG(salary)
FROM salesperson S, department D
WHERE S.DNUM = D.DNUM
AND D.NAME = 'CORPORATE'
SAMPLE RANDOM 10 PERCENT;
(EXPR)
---------------------
106250.000
--- 1 row(s) selected.
•
In this example, the SALESPERSON table is first sampled and then joined with the
DEPARTMENT table. This query computes the average salary of all the sales
people belonging to the CORPORATE department in a random sample of 10
percent of the sales employees.
SELECT AVG(salary)
FROM ( SELECT salary, dnum
FROM salesperson
SAMPLE RANDOM 10 PERCENT ) AS S, department D
WHERE S.DNUM = D.DNUM
AND D.NAME = 'CORPORATE';
(EXPR)
--------------------
37000.000
--- 1 row(s) selected.