Omron CX-Supervisor - Multiple Groupings Example; Grand Total Example; Grouped Parent Related to Grouped Child Example

Examples SECTION 12 Databases
199
COMPUTE rsOrders,
SUM(rsOrders.OrderTotal) AS CustTotal,
ANY(rsOrders.contactname) AS Contact
BY customerid
12-10-6 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
12-10-7 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
12-10-8 Grouped Parent Related to Grouped Child example:
SHAPE
(SHAPE {select * from customers} APPEND ((SHAPE
{select orders.*, year(orderdate) as OrderYear,
month(orderdate) as OrderMonth from orders} AS
rsOrders
COMPUTE rsOrders BY customerid, OrderYear,
OrderMonth) RELATE customerid TO customerid) AS
rsOrdByMonth ) AS rsCustomers
COMPUTE rsCustomers BY region
Note: The inner SHAPE clause in this example is identical to the statement used in
the Hierarchy with Aggregate example.
Note: The missing BY clause in the outer summary. This defines the Grand Total
because the parent rowset contains a single record with the grand total and a
pointer to the child recordset.