How to use Compute and ComputeBy Clauses in Query: SQL Programming

The COMPUTE clause, with the SELECT statement, is used to generate summary rows by using aggregate functions in the query results, in sql programming. The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data. The column on which the data is to be grouped is mentioned after the BY keyword.

The GROUP BY clause is used to generate a group summary report and does not produce individual table rows in the result set, whereas the COMPUTE and COMPUTE BY clauses generate the summary report with individual data rows from the table. In other words, the COMPUTE clause is used for control-break summary reporting applications that generate detailed information in the result set.

Syntax:

SELECT column_list
FROM table_name
ORDER BY column_name
COMPUTE aggregate_function (column_name) [, aggregate_function(column_name) …]
[BY column_name [, column_name]…]

Where,

• ORDER BY column_name specifies the name of the column(s) by which data in the result is to be sorted.
• COMPUTE aggregate_function specifies any row aggregate function from the aggregate function list.
• Column_name specifies the name of the column(s) for which the summary report is to be displayed.
• BY column_name specifies the name of the column(s) by which data is to be grouped.

The following SQL query uses the COMPUTE BY clause to calculate the average sickLeaveHours and VacationHours from the Employee table and display them based on Title, VacationHours, and SickLeaveHours:

SELECT Title, 'Average VacationHours' = VacationHours, 'Average SickLeaveHours' = SickLeaveHours FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE avg(VacationHours), Avg (SickLeaveHours) BY Title

In the preceding query, the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for both is retrieved, as shown in the following figure.

Consider another example, where you need to use the COMPUTE BY clause to calculate the subtotals of VacationHours and SickLeaveHours for each value in the Designation column. The COMPUTE clause calculates the grand total of VacationHours and SickLeaveHours:

SELECT Title, 'Total VacationHours' = VacationHours, 'Total SickLeaveHours' = SickLeaveHours
FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE sum (VacationHours), sum(SickLeaveHours) BY Title COMPUTE sum (VacationHours) , sum (SickLeaveHours)

In the preceding example, initially the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for the recruiter and stocker.

Both these keywords are not supported in the SQL Server management studio 2012, these queries can be executed in 2008 version.