Summarizing Data Using COMPUTE and COMPUTE BY

A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.

• The COMPUTE clause takes the following information:
• The optional BY keyword. This calculates the specified row aggregate on a per column basis.
• A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
• A column upon which to perform the row aggregate function.

The summary values generated by COMPUTE appear as separate result sets in the query results. The results of a query that include a COMPUTE clause are like a control-break report. This is a report whose summary values are controlled by the groupings, or breaks, that you specify. You can produce summary values for groups, and you can also calculate more than one aggregate function for the same group.

When COMPUTE is specified with the optional BY clause, there are two result sets for each group that qualifies for the SELECT:

Results Sets Generated by COMPUTE

The summary values generated by COMPUTE appear as separate result sets in the query results. The results of a query that include a COMPUTE clause are like a control-break report. This is a report whose summary values are controlled by the groupings, or breaks, that you specify. You can produce summary values for groups, and you can also calculate more than one aggregate function for the same group.
When COMPUTE is specified with the optional BY clause, there are two result sets for each group that qualifies for the SELECT:

• The first result set for each group has the set of detail rows that contain the select list information for that group.
• The second result set for each group has one row that contains the subtotals of the aggregate functions specified in the COMPUTE clause for that group.

When COMPUTE is specified without the optional BY clause, there are two result sets for the SELECT:

• The first result set for each group has all the detail rows that contain the select list information.
• The second result set has one row that contains the totals of the aggregate functions specified in the COMPUTE clause.

Examples

CREATE TABLE Classification (

    Classif_ID        integer  NOT NULL PRIMARY KEY,
    Classification    varchar(25))
GO

INSERT into Classification VALUES( 1,"Pop")

INSERT into Classification VALUES( 2,"Country")
INSERT into Classification VALUES( 3,"Alternative")
INSERT into Classification VALUES( 4,"Metal")

CREATE TABLE CD (

     CD_ID              integer  NOT NULL PRIMARY KEY,
     CD_Title           varchar(40),
     Composer_ID        integer  NOT NULL,
     Classif_ID         integer  NOT NULL,
     SalesPrice        money,
     AverageCost       money)
GO

INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)

INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
GO

With BY
SELECT CD.Classif_ID,Classification.Classification,
      CD.SalesPrice
FROM CD,Classification
WHERE CD.Classif_ID = Classification.Classif_ID
ORDER BY Classification.Classification
COMPUTE MIN(CD.SalesPrice) by Classification.Classification

Without BY

SELECT CD.Classif_ID,Classification.Classification,
      CD.SalesPrice
FROM CD,Classification
WHERE CD.Classif_ID = Classification.Classif_ID
ORDER BY Classification.Classification
COMPUTE MIN(CD.SalesPrice)

Comparing COMPUTE to GROUP BY
The following summarizes the differences between COMPUTE and GROUP BY:

• GROUP BY produces a single result set. There is one row for each group containing only the grouping columns and aggregate functions that show the subaggregate for that group. The select list can contain only the grouping columns and aggregate functions.
• COMPUTE produces multiple result sets. One kind of result set contains the detail rows for each group containing the expressions from the select list. The other type of result set contains the subaggregate for a group, or the total aggregate for the SELECT statement. The select list can contain expressions other than the grouping columns or aggregate functions. The aggregate functions are specified in the COMPUTE clause, not in the select list.

Download Article