SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-14
Examples of SAMPLE
•
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.
Note that the results of this query and some of the results of previous queries might
return null:
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)
--------------------
?
--- 1 row(s) selected.
For this query execution, the number of rows returned by the embedded query is
limited by the total number of rows in the SALESPERSON table. Therefore, it is
possible that no rows satisfy the search condition in the WHERE clause.