SybaseBlog

You have a table of client contracts and you want to know the sum of the value of those contracts by customer.

First let's set up our sample data.

create table ClientContracts (client_name varchar(32), work_detail varchar(32), amount money)
insert into ClientContracts values ("Johnson Autobody", "04AB Engine", 4000.00)
insert into ClientContracts values ("Johnson Autobody", "TL7 Axel", 325.00)
insert into ClientContracts values ("Libreville Engines", "L99X Transmission", 850.00)
insert into ClientContracts values ("Libreville Engines", "04AB Engine", 3250.00)
insert into ClientContracts values ("Libreville Engines", "Body Work", 298.95)

Ok, so you want to know the sum of the value of these contracts by customer? No problem, right? 'group by', right?

select client_name, sum(amount) from ClientContracts group by client_name
go

client_name                                               
-----------                      ------------------------ 
Johnson Autobody                                 4,325.00 
Libreville Engines                               4,398.95 

That does the job. But what if we wanted all the detail along with it? That's where 'compute' and 'compute by' come in handy. Observe:

select client_name, amount from ClientContracts order by client_name compute sum(amount) compute sum(amount) by client_name
go

client_name                      amount                   
-----------                      ------------------------ 
Johnson Autobody                                   325.00 
Johnson Autobody                                 4,000.00 
                                 sum                      
                                 ------------------------ 
                                                 4,325.00 
Libreville Engines                                 298.95 
Libreville Engines                                 850.00 
Libreville Engines                               3,250.00 
                                 sum                      
                                 ------------------------ 
                                                 4,398.95 
                                 sum                      
                                 ======================== 
                                                 8,723.95 

Awesome!

So what is compute?

Quoting the Transact-SQL User's Guide, we can see from the example above that compute "Generates summary values that appear as additional rows in the query results."

So basically you can see the details AND the summaries in a single query. If you just want the aggregate results, you can use 'group by', which will show the aggregate as a new column. But 'group by' will not show all the detail like compute will.

'Compute by', incidentally, is for subgroups. In the example above, it gave us the sub-totals whereas 'compute' gave us the grand total. 'Compute by' has an implicit 'group by.'

Here are some other notes and features of 'compute':
- You can have multiple computes
- You can compute expressions
- Computes have to on a column among those selected in the query
- Computes are often used in what are called 'control-break reports'. So named because the results are 'broken up' by the sub-totals and totals.
- You can use compute on any of these aggregate functions: sum, avg, min, max, count

For more information, consult the following two Sybase manuals:

Reference Manual Volume 2: Chapter 7: Commands
- Includes 7 good examples
http://download.sybase.com/pdfdocs/asg1250e/refman2.pdf

Transact-SQL User's Guide: Chapter 3 Using Aggregates, Grouping and Sorting
- Includes example of group by vs compute
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=2930?target=%25N%14_7889_START_RESTART_N%25