SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-45
Computing Aggregates Based on Specific
Conditions
SELECT LAST_NAME, FIRST_NAME, DEPTNUM,
CASE
WHEN DEPTNUM = "9000"
THEN SALARY * 1.10
WHEN DEPTNUM = "1000"
THEN SALARY * 1.12
ELSE SALARY
END
FROM EMPLOYEE;
LAST_NAME FIRST_NAME DEPTNUM (EXPR)
------------- ------------- ------- ----------
CHENG TINA 1000 72800.00
GONZALES LINDA 9000 83050.00
LEBLANC PIERRE 9000 40700.00
PETSKI STEVE 3500 50000.00
--- 4 row(s) selected.
Linda Gonzales and Pierre LeBlanc, both in Department 9000, received 10 percent
raises, and Tina Cheng, in Department 1000, received a 12 percent raise. Steve
Petski, who is in neither Department 9000 nor Department 1000, received no raise.
Computing Aggregates Based on Specific Conditions
You can use CASE to produce a report that contains aggregate values calculated for
groups, which are each selected using different criteria. By using CASE, you eliminate
the need to create a temporary table and insert data into it.
For example, suppose that a report counts the number of employees, by department,
whose salaries are in these ranges:
Range 1 < 20000
Range 2 < 50000
Range 3 < 200000