SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-46
Computing Aggregates Based on Specific
Conditions
Using CASE, you aggregate the necessary information with a single scan of the
EMPLOYEE table:
SET LIST_COUNT 0 ;
SELECT DEPTNUM,
SUM (CASE
WHEN SALARY < 20000 THEN 1
ELSE 0
END),
SUM (CASE
WHEN SALARY < 50000 THEN 1
ELSE 0
END),
SUM (CASE
WHEN SALARY < 200000 THEN 1
ELSE 0
END)
FROM PERSNL.EMPLOYEE
GROUP BY DEPTNUM ;
DETAIL DEPTNUM,
COL 2 AS I12 HEADING "SAL. < 20000",
COL 3 AS I12 HEADING "SAL. < 50000",
COL 4 AS I12 HEADING "SAL. < 200000" ;
LIST ALL ;
DEPTNUM SAL. < 20000 SAL. < 50000 SAL. < 200000
------- ------------ ------------ -------------
1000 1 3 5
1500 1 3 4
2000 0 4 5
. . . .
. . . .
4000 1 9 15
9000 0 1 2
--- 11 row(s) selected.
For information on SUM, see Aggregate Functions in Predicates on page 1-41. For
information on GROUP BY, see The GROUP BY Clause on page 1-7.
Another example that uses CASE with aggregates: Suppose that you have a table with
rows that contain each employee’s name, age, department, and number of cars. The
table contains no nulls and all numeric values for the number of cars. The primary key
is NAME.