RSS Feed

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:
CIDCustName
1AMIT
2SURESH
3KAPIL

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:
OIDCIDAmount
12100
22200
33300
43400
5111000

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:
CIDOIDCustNameAmount
1NULLAMITNULL
21SURESH100
22SURESH200
33KAPIL300
34KAPIL400
Null5NULL1000

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
1AMITNULL

Total:
Sum
Null

For CID=2 And OID=1,2
CIDCustNameAmount
2SURESH100
2SURESH200

Total:
Sum
300

For CID=3 And OID=3,4
CIDCustNameAmount
3KAPIL300
3KAPIL400

Total:
Sum
700

For CID=NULL & OID=5
CIDCustNameAmount
NULLNULL1000

Total:
Sum
1000