COMPUTE
TAGS: Computers, Databases, Programming, SQL, Tech
The COMPUTE clause tacks on a 1 row result set with aggregate information about its parent result set. Because of the multiple result sets, the results of COMPUTE cannot be used in cursors or as part of a SELECT INTO.
COMPUTE is provided for backwards compatibility; If possible, use ROLLBACK, CUBE, or database APIs that provide analysis for data warehouses and data marts (eg OLE DB for Analysis Services or ADO MD).
The syntax is as follows. The parts in bold were not included in my section on the SELECT Command or the GROUP BY Clause.
SELECT [ALL | DISTINCT] [TOP n [PERCENT]] SelectList [INTO NewTable] [FROM TableSources] [WHERE WhereRowSearchCondition] [ GROUP BY [ALL] GroupList [WITH [CUBE | ROLLUP]] [HAVING HavingRowSearchCondition] ] [ORDER BY OrderList [ASC | DESC]] [ COMPUTE ComputeList [BY ComputeByList] ]
The COMPUTE clause tacks on a 1 row result set with aggregate information about its parent result set as per the ComputeList. Here are a few particulars about the COMPUTE clause:
-
The ComputeList can only consist of items with this syntax:
{ AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } (ComputeListExprssion) - The aggregate functions available to COMPUTE are a subset of the aggregate functions available to the DBMS.
- These aggregate functions will ignore null values.
-
A
ComputeListExpressionmust also appear in theSelectList. No aliases can be used.
The BY clause breaks up its parent result set into result sets of varying rows as per the ComputeByList. Here are a few particulars about the BY clause:
-
If
BYis used, thenORDER BYmust also be used. -
The
ComputeByListmust either match theOrderList, or match the beginning of theOrderList.
EGs
Just SELECT
This returns a 18 row result set of the whole table.
USE pubs SELECT type, price, advance FROM titles ORDER BY type
type price advance ------------ --------------------- --------------------- business 19.9900 5000.0000 business 11.9500 5000.0000 business 2.9900 10125.0000 business 19.9900 5000.0000 mod_cook 19.9900 .0000 mod_cook 2.9900 15000.0000 popular_comp 22.9500 7000.0000 popular_comp 20.0000 8000.0000 popular_comp NULL NULL psychology 21.5900 7000.0000 psychology 10.9500 2275.0000 psychology 7.0000 6000.0000 psychology 19.9900 2000.0000 psychology 7.9900 4000.0000 trad_cook 20.9500 7000.0000 trad_cook 11.9500 4000.0000 trad_cook 14.9900 8000.0000 UNDECIDED NULL NULL (18 row(s) affected)
SELECT with GROUP BY
This reduces the previous 18 row result set of the whole table into a 6 row result set with aggregate information.
USE pubs SELECT type, SUM(price) AS SUMprice, SUM(advance) AS SUMadvance FROM titles GROUP BY type ORDER BY type
type SUMprice SUMadvance ------------ --------------------- --------------------- business 54.9200 25125.0000 mod_cook 22.9800 15000.0000 popular_comp 42.9500 15000.0000 psychology 67.5200 21275.0000 trad_cook 47.8900 19000.0000 UNDECIDED NULL NULL (6 row(s) affected)
SELECT with COMPUTE
This makes 2 result sets:
- The18 row result set of the whole table.
-
A 1 row result set with aggregate information about its parent result set as per the
ComputeList, i.e.SUM(price), SUM(advance).
USE pubs SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance)
type price advance
------------ --------------------- ---------------------
business 19.9900 5000.0000
business 11.9500 5000.0000
business 2.9900 10125.0000
business 19.9900 5000.0000
mod_cook 19.9900 .0000
mod_cook 2.9900 15000.0000
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULL NULL
psychology 21.5900 7000.0000
psychology 10.9500 2275.0000
psychology 7.0000 6000.0000
psychology 19.9900 2000.0000
psychology 7.9900 4000.0000
trad_cook 20.9500 7000.0000
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
UNDECIDED NULL NULL
sum
=====================
236.2600
sum
=====================
95400.0000
(19 row(s) affected)
SELECT with COMPUTE BY
This takes the18 row result set of the whole table and breaks it up into 6 result sets of varying rows as per the ComputeByList, i.e. type.
Each of those 6 result sets has a 1 row result set with aggregate information about its parent result set as per the ComputeList, i.e. SUM(price), SUM(advance).
The final result is 12 result sets.
USE pubs SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance) BY type
type price advance
------------ --------------------- ---------------------
business 19.9900 5000.0000
business 11.9500 5000.0000
business 2.9900 10125.0000
business 19.9900 5000.0000
sum
=====================
54.9200
sum
=====================
25125.0000
type price advance
------------ --------------------- ---------------------
mod_cook 19.9900 .0000
mod_cook 2.9900 15000.0000
sum
=====================
22.9800
sum
=====================
15000.0000
type price advance
------------ --------------------- ---------------------
popular_comp 22.9500 7000.0000
popular_comp 20.0000 8000.0000
popular_comp NULL NULL
sum
=====================
42.9500
sum
=====================
15000.0000
type price advance
------------ --------------------- ---------------------
psychology 21.5900 7000.0000
psychology 10.9500 2275.0000
psychology 7.0000 6000.0000
psychology 19.9900 2000.0000
psychology 7.9900 4000.0000
sum
=====================
67.5200
sum
=====================
21275.0000
type price advance
------------ --------------------- ---------------------
trad_cook 20.9500 7000.0000
trad_cook 11.9500 4000.0000
trad_cook 14.9900 8000.0000
sum
=====================
47.8900
sum
=====================
19000.0000
type price advance
------------ --------------------- ---------------------
UNDECIDED NULL NULL
sum
=====================
sum
=====================
(24 row(s) affected)
SELECT with GROUP BY and COMPUTE
This makes 2 result sets:
- A 6 row result set with aggregate information based upon the 18 row result set of the whole table.
-
A 1 row result set with aggregate information about its parent result set as per the
ComputeList, i.e.SUM(price), SUM(advance).
USE pubs SELECT type, SUM(price) AS SUMprice, SUM(advance) AS SUMadvance FROM titles GROUP BY type ORDER BY type COMPUTE SUM(SUM(price)), SUM(SUM(advance))
type SUMprice SUMadvance
------------ --------------------- ---------------------
business 54.9200 25125.0000
mod_cook 22.9800 15000.0000
popular_comp 42.9500 15000.0000
psychology 67.5200 21275.0000
trad_cook 47.8900 19000.0000
UNDECIDED NULL NULL
sum
=====================
236.2600
sum
=====================
95400.0000
(7 row(s) affected)
EG SELECT with GROUP BY and COMPUTE BY
This takes the 6 row result set with aggregate information based upon the 18 row result set of the whole table and and breaks it up into 6 1 row result sets.
Each of those 6 result sets has a 1 row result set with aggregate information about its parent result set as per the ComputeList, i.e. SUM(price), SUM(advance).
The final result is 12 result sets. It is absurd to use both GROUP BY and COMPUTE BY since you get a bunch of 1 row result sets, you might as well just use GROUP BY and COMPUTE.
USE pubs SELECT type, SUM(price) AS SUMprice, SUM(advance) AS SUMadvance FROM titles GROUP BY type ORDER BY type COMPUTE SUM(SUM(price)), SUM(SUM(advance)) BY type
type SUMprice SUMadvance
------------ --------------------- ---------------------
business 54.9200 25125.0000
sum
=====================
54.9200
sum
=====================
25125.0000
type SUMprice SUMadvance
------------ --------------------- ---------------------
mod_cook 22.9800 15000.0000
sum
=====================
22.9800
sum
=====================
15000.0000
type SUMprice SUMadvance
------------ --------------------- ---------------------
popular_comp 42.9500 15000.0000
sum
=====================
42.9500
sum
=====================
15000.0000
type SUMprice SUMadvance
------------ --------------------- ---------------------
psychology 67.5200 21275.0000
sum
=====================
67.5200
sum
=====================
21275.0000
type SUMprice SUMadvance
------------ --------------------- ---------------------
trad_cook 47.8900 19000.0000
sum
=====================
47.8900
sum
=====================
19000.0000
type SUMprice SUMadvance
------------ --------------------- ---------------------
UNDECIDED NULL NULL
sum
=====================
sum
=====================
(12 row(s) affected)
Page Modified: (Hand noted: 2007-10-12 19:44:03Z) (Auto noted: 2010-12-24 22:50:43Z)