Aggregation
Syntax:
SELECT T@S.f(F), f(x), f(x1:x2:…) ...... FROM T
Parameter:
|
T |
Table name |
|
f(x) |
Aggregate function f on general field expression x |
|
T@S.f(F) |
The aggregate expression on general field F of table T’s sub table S; f is the aggregate function |
|
f(x1:x2:…) |
xi are general field expressions coming from different tables; these fields will be aligned between each other to generate a target field |
Example:
|
SELECT SellerID,sum(Amount) FROM ReturnedPmt BY SellerID |
Group by SellerID and sum values under Amount field |
|
SELECT Customer@ReturnedPmt.sum(Amount),CustID FROM Customer BY CustID |
Sum values under Amount field in Customer table’s sub table ReturnedPmt by grouping field CustID |
|
SELECT sum(Orders.Amount: ReturnedPmt.Amount) ON Day FROM Orders BY ShipDate UNION ReturnedPmt BY Date |
Sum values under Amount field in Orders table and those under Amount field in ReturnedPmt table by dimension Day |
Tip:
1) A primary table is a table where part of the primary key is pointed by the foreign key in another table; a dimension table of a level functionwith a single-field primary key; the primary table’s primary table; a homo-dimension table’s primary table; the primary table’s homo-dimension table. The last case requires that primary keys of the primary table and the sub table correspond in order.