Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Using Aggregate Functions with COMPUTE

COMPUTE is a neat extension of the SELECT statement that lets you generate summary reports in Query Analyzer. Again, most companies will have a better tool for reporting, but if you want "quick-and-dirty" reports, COMPUTE is there to help.

COMPUTE and COMPUTE BY do almost the same thing as CUBE/ROLLUP, but without using GROUP BY. This provides the extra flexibility of seeing the individual values, as well as summarized values and running totals. The disadvantage of using COMPUTE is that it generates multiple result sets. Notice that you must use the ORDER BY clause if you intend to use COMPUTE BY. Further, columns specified in the COMPUTE BY clause must be in the same order as in ORDER BY.

The following example generates the list of sales of each title, along with the total sales and average quantities sold at the end:

SELECT 
        qty,
        title_id,
        DATEPART(YEAR, ord_date) AS year_sold
FROM sales
COMPUTE SUM(qty),  AVG(qty)

Results:

qty    title_id year_sold   
------ -------- ----------- 
5      BU1032   1994
3      PS2091   1994
50     PC8888   1993
75     PS2091   1994
10     PS2091   1994
55     BU2075   2002
40     TC3218   1992
20     TC4203   1992
20     TC7777   1992
20     PS2091   1994
25     MC3021   1994
20     PS1372   1993
25     PS2106   1993
15     PS3333   1993
25     PS7777   1993
15     BU7832   1993
10     MC2222   1993
35     BU2075   1993
15     MC3021   1994
10     BU1032   1994
25     BU1111   1993
30     PC1035   1993

sum
===========
548

avg
===========
24

Now we can go one step further and provide running totals by using COMPUTE BY instead of just using COMPUTE. I limited the output to only the top five records to save some room:

SELECT 
       TOP 5
qty,
       title_id,
       DATEPART(YEAR, ord_date) AS year_sold
FROM sales
ORDER BY 2, 3
COMPUTE SUM(qty),  AVG(qty) BY title_id, year_sold

Results:

qty    title_id year_sold   
------ -------- ----------- 
5      BU1032   1994
10     BU1032   1994

sum
===========
15

avg
===========
7


qty    title_id year_sold   
------ -------- ----------- 
25     BU1111   1993

sum
===========
25

avg
===========
25


qty    title_id year_sold   
------ -------- ----------- 
35     BU2075   1993

sum
===========
35

avg
===========
35


qty    title_id year_sold   
------ -------- ----------- 
55     BU2075   2002

sum
===========
55

avg
===========
55
  • + Share This
  • 🔖 Save To Your Account