Omron CX-SUPERVISOR - V2.0 - Grand Total Example; Multiple Groupings Example

OMRON CHAPTER 12 – Databases
Release 2.0 Page 215
SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS rsOrders
COMPUTE rsOrders,
SUM(rsOrders.OrderTotal) AS CustTotal,
ANY(rsOrders.contactname) AS Contact
BY customerid
Note: The inner SHAPE clause in this example is identical to the statement used in the
Hierarchy with Aggregate example.
Multiple Groupings example:
SHAPE
(SHAPE {select customers.*,
od.unitprice * od.quantity as ExtendedPrice from (customers
inner join orders
on customers.customerid = orders.customerid) inner join
[order details] as od on orders.orderid = od.orderid}
AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
ANY(rsDetail.region) AS Region,
SUM(rsDetail.ExtendedPrice) AS CustTotal,
rsDetail
BY customerid) AS rsCustSummary
COMPUTE rsCustSummary
BY Region
Grand Total example:
SHAPE
(SHAPE {select customers.*,
od.unitprice * od.quantity as ExtendedPrice from (customers inner
join orders on customers.customerid = orders.customerid) inner
join
[order details] as od on orders.orderid = od.orderid}
AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
SUM(rsDetail.ExtendedPrice) AS CustTotal,
rsDetail
BY customerid) AS rsCustSummary
COMPUTE SUM(rsCustSummary.CustTotal) As GrandTotal,
rsCustSummary