Group & aggregate
Syntax:
SELECT T.f(...),......, T.F
ON D
FROM T WHERE…BY T.F.....
HAVING…..
Parameter:
|
T.f(...) |
Aggregate function |
|
T.F |
Grouping field |
|
D |
Dimension |
|
BY T.F |
Grouping field |
|
HAVING |
Filtering condition on each or all grouped subsets |
Example:
|
SELECT CustomerID,count(OrderID) OrderNum ,avg(Amount) Avg_Amount FROM Orders BY CustomerID HAVING OrderNum>20 |
Group Orders table by CustomerID, count orders and calculate the average Amount value in each group, and return records where the order count exceeds 20
Use alias in the select expression after HAVING when the field has one |
|
|
SELECT count(OrderID),sum(Amount) ON Year ,EMPLOYEE FROM Orders BY ShipDate#Year,EmployeeID |
Group by the year in the ship date and employee ID, and count orders and sum order amounts in each year; year after
BY is the level functionwithin Day |
|
Tips:
1) ON operator can be absent when only one table is involved.
2) The field after BY should correspond that after ON operator; automatically use the level functionto correspond to the dimension in ON clause when there is one in BY clause.