INF: COMPUTE and the Microsoft SQL Server ODBC driver

Last reviewed: April 9, 1997
Article ID: Q157800

The information in this article applies to:

Microsoft SQL Server, versions 4.2x, 6.0, and 6.5
Microsoft Open Database Connectivity, version 2.5

SUMMARY

The COMPUTE BY clause in a SQL Server statement generates subtotals within result sets, whereas the COMPUTE clause generates a total at the end of the result set. The Microsoft SQL Server ODBC driver presents this total back to the calling application by generating multiple result sets for the SELECT.

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.

Additional query words:
Keywords : kbinterop kbprg kbusage SSrvProg
Version : 2.5 2.65 4.2x 6.0 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.