Note that with COMPUTE and COMPUTE BY, the rowcount reported by the server includes the computed row, but doesn’t include extra rows for the === lines or the name of the aggregate.

You can use COMPUTE BY and COMPUTE together to create subtotal and total information. I’ll use the same query as before, but this time I will add a COMPUTE clause to the very end, in order to get the maximum advance for all books. (I’ll leave out the rows in the middle for the sake of simplicity.)

select          title_id,  type,  advance
from           titles
order   by   type
compute       max(advance)    by  type
compute        max(advance)

title_id type                advance
--------          ------------     --------------------------
MC3026      UNDECIDED         (null)

                             max
                              ==========================
                              (null)
[…]

title_id type                advance
--------         ------------      --------------------------
TC3218         trad_cook     7,000.00
TC4203         trad_cook     4,000.00
TC7777         trad_cook     8,000.00

                             max
                              ==========================
                              8,000.00
                             max
                              ==========================
                             15,000.00

Multiple COMPUTE BY Clauses

If you list multiple columns in the COMPUTE BY clause, all of them must appear in the ORDER BY. Additionally, the order by which the columns are sorted is important. Suppose you have a table with columns price, c1, c2, c3, c4, and c5, and you write a query like this:

select c1, c2, c3, c4, c5, price
from   t1
order by c1, c2, c3

Assuming you want to calculate a SUM of these columns, the only choices you have for the COMPUTE BY clause are

compute sum(price) by c1

compute sum(price) by c1, c2

compute sum(price) by c1, c2, c3

This is because the rows are first placed into a worktable and then the server works its way through that table to add the computed results. If all like rows are not listed sequentially (by using the ORDER BY), the COMPUTE won’t know on which rows to operate.

COMPUTE Is Strange

Compute is different from other clauses in a lot of ways:

•  It must be handled differently in application programs.

•  Its calculations take effect on items in the result set, not on items in the underlying tables.

•  COMPUTE is a T-SQL extension to the ANSI standard.

These differences, especially the first, have caused COMPUTE to fall out of favor with most users. In application programs, it is common to see aggregate results generated by the client, by reading each value and computing the SUM, AVG, MIN, MAX, or COUNT without asking for a computed result. The only time COMPUTE sees real use these days is by ad-hoc users with access to ISQL/w or another query tool, and in legacy SQL code.

---

Technical Note:  How applications benefit from aggregates
Some client programs calculate aggregates on their own, simply by asking for all the rows from the server and handling the math once the values are retrieved. This approach is fine when the normal rows are used in the client program in addition to the aggregates.

If all you want is an aggregate, however, it’s much more efficient for the server to do the work, especially with respect to network bandwidth.

---

HAVING

The HAVING clause allows you to restrict rows that are returned, without restricting the rows that take part in the calculation of those rows. HAVING can be used only with GROUP BY to filter out aggregate results.

In previous versions of the server that did not maintain strict ANSI compliance, HAVING would function like the WHERE clause when used with GROUP BY. Some users exclusively used HAVING to evaluate all conditions, which led to horrible performance. If you see legacy code such as this, you can chuckle to yourself, knowing that the original author didn’t understand the difference between HAVING and WHERE, as you do.

Now, let’s take a look at a HAVING example. To see all book types with average prices over $12, use this query:

select        type,   avg(price)
from         titles
group  by  type
having       avg(price) > $12

type
------------                --------------------------
business         13.73
popular_comp     21.48
psychology       13.50
trad_cook        15.96

This query starts out the same way the other queries in this chapter do: they calculate the average prices of each type of book in the titles table. Once that result set is complete, but before returning the results, the HAVING clause is evaluated. The results are searched, and any type of book with an average price greater than $12 is returned. Although modern cookbooks are initially included in the intermediate result set, the HAVING clause culls out this type.

Order of Evaluation

In a query including WHERE, HAVING, GROUP BY, and COMPUTE, here is how the server evaluates the three clauses:

1.  Rows meeting the criteria in the WHERE clause are marked for inclusion.

2.  Groups are assembled from rows meeting those conditions and placed in a worktable.

3.  The contrived rows are compared against the HAVING clause. Those that pass the HAVING criteria are selected into a new worktable.

4.  The COMPUTE runs against whatever data remains, and the final result set is returned to the client process.

SELECT Syntax

Here is the syntax for the SQL SELECT, incorporating all elements that you have learned to date:

SELECT [DISTINCT] <column | constant>[, <column | constant> …]
FROM   <table list>
WHERE  <Boolean condition> [<AND | OR> <Boolean condition> …]
GROUP BY <Column list> [WITH <CUBE | ROLLUP>]
HAVING <Boolean condition> [<AND | OR> <B oolean condition> …]
ORDER BY <column name> | <ordinal column position> } <column alias>
        [, <name>, <ordinal>, <alias> …]
COMPUTE<aggregate> BY <Ordered column> [, <agg> BY <orderedcol> …]
COMPUTE<aggregate>