MORE INFORMATION
The following example from the Transact-SQL Reference uses COMPUTE BY and
COMPUTE to generate subtotals and the total:
SELECT title = CONVERT(char(20), title), type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)
These statements cause a subtotal calculation for the average price and sum
of advances for each book type, and then produce a final total sum of both
the price and advance data. The following ODBCTest GetDataAll output shows
how the ODBC driver presents these subtotals and totals back to the calling
application as separate result sets intermixed with the 'primary' result
set:
"title", "type", "price", "advance"
"Onions, Leeks, and G", "trad_cook ", 20.9500, 7000.0000
"Fifty Years in Bucki", "trad_cook ", 11.9500, 4000.0000
"Sushi, Anyone? ", "trad_cook ", 14.9900, 8000.0000
3 rows fetched from 4 columns.
"AVG", "SUM"
15.9633, 19000.0000
1 row fetched from 2 columns.
"title", "type", "price", "advance"
"Computer Phobic AND ", "psychology ", 21.5900, 7000.0000
"Is Anger the Enemy? ", "psychology ", 10.9500, 2275.0000
"Life Without Fear ", "psychology ", 7.0000, 6000.0000
"Prolonged Data Depri", "psychology ", 19.9900, 2000.0000
"Emotional Security: ", "psychology ", 7.9900, 4000.0000
5 rows fetched from 4 columns.
"AVG", "SUM"
13.5040, 21275.0000
1 row fetched from 2 columns.
"title", "type", "price", "advance"
"But Is It User Frien", "popular_comp", 22.9500, 7000.0000
"Secrets of Silicon V", "popular_comp", 20.0000, 8000.0000
2 rows fetched from 4 columns.
"AVG", "SUM"
21.4750, 15000.0000
1 row fetched from 2 columns.
"title", "type", "price", "advance"
"Silicon Valley Gastr", "mod_cook ", 19.9900, .0000
"The Gourmet Microwav", "mod_cook ", 2.9900, 15000.0000
2 rows fetched from 4 columns.
"AVG", "SUM"
11.4900, 15000.0000
1 row fetched from 2 columns2 .
"title", "type", "price", "advance"
"The Busy Executive's", "business ", 19.9900, 5000.0000
"Cooking with Compute", "business ", 11.9500, 5000.0000
"You Can Combat Compu", "business ", 2.9900, 10125.0000
"Straight Talk About ", "business ", 19.9900, 5000.0000
4 rows fetched from 4 columns.
"AVG", "SUM"
13.7300, 25125.0000
1 row fetched from 2 columns.
"SUM", "SUM"
236.2600, 95400.0000
1 row fetched from 2 columns.
You can see from the output above that the driver presents the first result
set for the rows from books having the first book type. It then produces a
second result set with the two COMPUTE BY columns for the AVG(price) and
SUM(advance) for this first set of books. Then it produces a third result
set for the next group of books, and a fourth result set with the COMPUTE
BY subtotals for that group. The driver keeps interleaving these result
sets until the end, when it produces the final result set with the total
for the COMPUTE SUM(price), SUB(advance) clause.
Applications running SQL Server commands with COMPUTE BY and COMPUTE
clauses must be coded to handle the multiple result sets returned by the
driver.
Note that the Microsoft SQL Server ODBC driver only supports COMPUTE BY or
COMPUTE with the default forward_only, read_only cursors with a rowset size
of one. The driver implements all other cursor types (dynamic, static, or
keyset) by using server cursors. SQL Server's server cursors do not support
commands that generate multiple result sets.
If an application uses the ODBC Cursor Library with the Microsoft SQL
Server ODBC driver, it can open a static cursor on selects with COMPUTE BY
or COMPUTE. However, the application cannot scroll around the complete
result set, because the query's data has been broken into multiple result
sets by the COMPUTE and COMPUTE BY clauses. For example, if an application
sets the option for static cursors, runs the above statements, and then
does a SQLExtendedFetch(hstmt, SQL_FETCH_LAST), the following row will be
returned:
"Sushi, Anyone? ", "trad_cook ", 14.9900, 8000.0000
That row is returned because it is the last row in the first result
returned by the select, which is the following:
"Onions, Leeks, and G", "trad_cook ", 20.9500, 7000.0000
"Fifty Years in Bucki", "trad_cook ", 11.9500, 4000.0000
"Sushi, Anyone? ", "trad_cook ", 14.9900, 8000.0000
ODBC applications must deal with the output of selects containing COMPUTE
BY and COMPUTE as a series of discrete result sets, not as a single
continuous result set.