Intro

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 ComputeListExpression must also appear in the SelectList . 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 BY is used, then ORDER BY must also be used.
  • The ComputeByList must either match the OrderList, or match the beginning of the OrderList.

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)


GeorgeHernandez.comSome rights reserved