Sybase Compute Clauses

select salesman,
       office,
     invoice
   from turnover
order by salesman
compute sum(invoice), avg(invoice) by salesman

which produces something like...

salesman     office       invoice
----------- ----------- -----------
Al Smith    Slough         1,252.65
Al Smith    Slough         1,899.56
Al Smith    Slough         1,154.43
Al Smith    Slough         2,312.92

Compute Result:
------------------------ ------------------------
                6,619.56                 1,654.89

salesman    office      invoice
----------- ----------- -----------
Fred Bloggs Swindon        2,567.21
Fred Bloggs Swindon        1,021.78
Fred Bloggs Swindon        2,280.69

Compute Result:
------------------------ ------------------------
                5,869.68                 1,956.56

and so on...

Rules for Compute Clauses

You can use the following aggregates in a compute clause: avg(); count(); max(); min(); sum().
Every column that you want in the compute clause must be present in the select clause,
You can't use the distinct keyword (well, you wouldn't, anyway, would you?)
You can't use select into because computes don't generate normal rows.
You may have multiple compute clauses in a single query
You may use multiple instances of the same aggregate in a single compute clause
You may use several different aggregates in a single compute clause

And Now for Something...

There is a compute by which matches the group by clause. It works in a very similar manner but has the extra rule that you can only use it if you also have an order by clause in your query. In order for this to work you must observe the following additional rules...

The columns in the compute by clause must be identical to, or a subset of, those listed in the order by clause.
The columns must be in the same left-to-right order in both clauses.
The columns must start with the same expression, and not skip any expressions.

Provided all the above rules are met, you may have both a compute and a compute by clause in the same query. This means that something like the following is entirely valid...

select product, charges, invoice
from saleslog
where product like "curtain%"
order by product
compute sum(charges), sum(invoice) by product
compute sum(charges), sum(invoice)

product        charges              invoice
--------------- -----------------    ------------
curtains                2.99       15,000.00
curtain fitting              19.99            0.00

Compute Result:
--------------- ---------
          22.98 15,000.00

product        charges              invoice
--------------- -----------------    ------------
curtains                   11.95        4,000.00
curtains                   14.99        8,000.00
curtains                   20.95        7,000.00

Compute Result:
--------------- ---------
          47.89 19,000.00

Compute Result:
--------------- ---------
          70.87 34,000.00

Caveat emptor...

As with the other Sybase aggregate queries, computes are a very powerful feature. Again, as with the other aggregates, it's possible to come unstuck really easily if you aren't fully aware of what the data should look like after running a compute. Always test aggregate queries on small but representative data sets to ensure that they really are doing what you intend them to.