Mega Code Archive

 

Categories / MSSQL / Analytical Functions

 

Use multiple aggregate functions in a COMPUTE clause

1> 2> CREATE TABLE project   (project_no   CHAR(4) NOT NULL, 3>                         project_name CHAR(15) NOT NULL, 4>                         budget FLOAT NULL) 5> GO 1> insert into project values ('p1', 'Search Engine',        120000.00) 2> insert into project values ('p2', 'Programming',          95000.00) 3> insert into project values ('p3', 'SQL',                  186500.00) 4> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from project 2> GO project_no project_name    budget ---------- --------------- ------------------------ p1         Search Engine                     120000 p2         Programming                        95000 p3         SQL                               186500 (3 rows affected) 1> 2> -- Use multiple aggregate functions in a COMPUTE clause. 3> 4> SELECT project_no, budget 5>        FROM project 6>        WHERE budget < 150000 7>       COMPUTE SUM(budget), AVG(budget) 8> 9> drop table project 10> GO project_no budget ---------- ------------------------ p1                           120000 p2                            95000            sum            ========================                              215000            avg            ========================                              107500 1>