- Function Determinism
- Types of Built-in Functions
- Aggregate Functions
- Statistical Aggregate Functions
- Other Aggregate Functions
- Using Aggregate Functions with COMPUTE
- Summary
Like this article? We recommend
Like this article? We recommend
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