Thursday, December 20

Compute and Compute By Clause- SQL

Compute Clause:

It 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 sub-aggregate 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.


Compute By Clause:

It can be used to calculate summary values of the result set on a group of data. The column name by which the data should be grouped is mentioned after BY keyword. The Group By clause produces group summary report, while Compute and Compute By produces summary report with individual data rows from table

Syntax of Compute:

SELECT Column_names
FROM Table_name
Order By Column_name(s)      //use only when need
COMPUTE aggregate_function(column_name)  //more than one 
                     aggregate_function(column_name) can be added

Syntax of Compute By:

SELECT Column_names
FROM Table_name
Order By Column_name(s)      //use only when need
COMPUTE aggregate_function(column_name)  //more than one
                     aggregate_function(column_name) can be added
BY Column_name(s)


**Order By column_name specifies the name of the column(s) by which data to be sorted in the result set.

**Compute aggregate_function specifies aggregate function that to be done on the row

**column_name specifies the name of the column for which the summary reports has to be produced

**By column_name(s) specifies the name column(s) by which data is to grouped.