COMPUTE: calculate summary values that appear as additional rows in the result of a query
11>
12> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
13> project_no CHAR(4) NOT NULL,
14> job CHAR (15) NULL,
15> enter_date DATETIME NULL)
16>
17> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
18> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
19> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
20> insert into works_on values (2, 'p2', NULL, '1998.6.1')
21> insert into works_on values (3, 'p2', NULL, '1997.12.15')
22> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
23> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
24> insert into works_on values (6, 'p1', NULL, '1998.8.1')
25> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
26> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
27> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
28> -- COMPUTE clause uses aggregate functions (MIN, MAX, SUM, AVG, and COUNT) to
calculate summary values that appear as additional rows in the result of a query.
29>
30> SELECT emp_no, project_no, enter_date
31> FROM works_on
32> WHERE project_no = 'p1' OR project_no = 'p2'
33> COMPUTE MIN(enter_date)
34> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no project_no enter_date
----------- ---------- -----------------------
1 p1 1997-10-01 00:00:00.000
2 p2 1998-02-15 00:00:00.000
2 p2 1998-06-01 00:00:00.000
3 p2 1997-12-15 00:00:00.000
5 p1 1998-04-15 00:00:00.000
6 p1 1998-08-01 00:00:00.000
7 p2 1999-02-01 00:00:00.000
7 p1 1998-01-04 00:00:00.000
min
=======================
1997-10-01 00:00:00.000
1>
2> drop table works_on
3> GO
Related examples in the same category