Transact-SQL User's Guide

Home

Using different aggregates in the same compute clause

Transact-SQL User's Guide

Chapter 3 Using Aggregates, Grouping, and Sorting

Summarizing groups of data: the compute clause

Grand values: compute without by

You can use the compute keyword without by to generate grand totals, grand counts, and so on.

This statement finds the grand total of the prices and advances of all types of books that cost more than $20:

select type, price, advance 
from titles 
where price > $20 
compute sum(price), sum(advance)

type         price            advance 
------------ ----------------  ------------- 
popular_comp            22.95      7,000.00 
psychology              21.59      7,000.00 
trad_cook               20.95      7,000.00 
 
Compute Result:
--------------- ---------
          65.49 21,000.00 
 
(4 rows affected)

You can use a compute with by and a compute without by in the same query. The following query finds the sum of prices and advances by type and then computes the grand total of prices and advances for all types of books.

select type, price, advance 
from titles 
where type like "%cook" 
order by type 
compute sum(price), sum(advance) by type 
compute sum(price), sum(advance)

type         price                advance
-----------  -----------------    ------------
mod_ cook                 2.99       15,000.00
mod_cook                 19.99            0.00
 
Compute Result:
--------------- ---------
          22.98 15,000.00
 
type         price                advance
-----------  -----------------    ------------
trad_cook                11.95        4,000.00
trad_cook                14.99        8,000.00
trad_cook                20.95        7,000.00
 
Compute Result:
--------------- ---------
          47.89 19,000.00
  
Compute Result:
--------------- ---------
          70.87 34,000.00
 
(8 rows affected)


	Expand Search