COMPUTE & COMPUTE BY Clause

COMPUTE & COMPUTE BY Clause

COMPUTE generates totals, that appear as an additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.

The general syntax for COMPUTE is:

[ COMPUTE 
    { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } 
  ( expression ) } [ ,...n ] 
    [ BY expression [ ,...n ] ] 
]

[Source of syntax MSDN]

Example of COMPUTE and COMPUTE BY:

We have 2 tables viz. Customer and Orders. Customer has 2 columns i.e. CID and CustName and Orders has 3 columns i.e. OID, CID and Amount.

1.Create Customer table
CREATE TABLE Customer (CID int PRIMARY KEY, CustName Varchar(15))

2.Insert records in Customer

INSERT INTO Customer
VALUES(1,'AMIT')
INSERT INTO Customer
VALUES(2,'SURESH')
INSERT INTO Customer
VALUES(3,'KAPIL')

3.Show Records
SELECT * FROM Customer

Result:

CID	CustName
1	AMIT
2	SURESH
3	KAPIL

4.Create Orders Table
CREATE TABLE Orders(OID INT,CID INT,Amount INT)

5. Insert records in Orders

INSERT INTO Orders
VALUES(1,2,100)
INSERT INTO Orders
VALUES(2,2,200)
INSERT INTO Orders
VALUES(3,3,300)
INSERT INTO Orders
VALUES(4,3,400)
INSERT INTO Orders
VALUES(5,11,1000)

5.Show Records
SELECT * FROM Orders

Result:

OID	CID	Amount
1	2	100
2	2	200
3	3	300
4	3	400
5	11	1000

Query:
1) Calculate the total amount in Orders with all the customers in Customer (using COMPUTE).
2) Calculate the total amount in Orders for Each Customer (Group By CID) (using COMPUTE BY).

1)For COMPUTE

SELECT c.CID,o.OID,c.CustName,O.Amount
FROM Customer AS c
FULL OUTER JOIN Orders AS o
ON c.CID=o.CID
COMPUTE SUM(o.Amount)

Result:

CID	OID	CustName	Amount
1	NULL	AMIT	NULL
2	1	SURESH	100
2	2	SURESH	200
3	3	KAPIL	300
3	4	KAPIL	400
Null	5	NULL	1000

Total:

sum

2000

2)For COMPUTE BY

SELECT c.CID,c.CustName,o.Amount
FROM Customer AS c
FULL OUTER JOIN Orders AS o
ON c.CID=o.CID
ORDER BY o.CID
COMPUTE SUM(o.Amount) BY o.CID

Result:
For CID=1 And OID=NULL

1

AMIT

NULL

Total:

Sum

Null

For CID=2 And OID=1,2

CID	CustName	Amount
2	SURESH	100
2	SURESH	200

Total:

Sum

300

For CID=3 And OID=3,4

CID	CustName	Amount
3	KAPIL	300
3	KAPIL	400

Total:

Sum

700

For CID=NULL & OID=5

CID	CustName	Amount
NULL	NULL	1000

Total:

Sum

1000