In the following example, we will use aggregation to calculate the average profit that our company made for a number of products, in order to locate our best product. We are assuming that the database provides the columns containing the price we got from the buyers at each transaction (Sales) and the price our company paid for the raw material (Cost) at that time. We also have a column containing the type of the product sold at each transaction (Type).
Define a column element called "Average Profit" as follows:
We must also define a column element "Type" from Type which returns the product type.
If we define an information link using these three columns, the following SQL will be generated (notice the GROUP BY clause):
SELECT tmp1."AVERAGEPROFIT", E1."TYPE" AS "TYPE" FROM "Sales"."dbo"."SalesandCost" S1 SELECT AVG(E1."Sales" - E1."Cost") AS "AVERAGEPROFIT", E1."TYPE" AS col1 FROM "Sales"."dbo"."SalesandCost" S1 GROUP BY E1."TYPE" ) tmp1 WHERE (E1."TYPE" = tmp1.col1) AND <conditions> |