SQL – GROUP BY 


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:

id_group_by_example.png

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>

See also:

Creating a Column Element

Using Aggregation