Switch to: V14V13V12V11V10V9V8V7V6V5

GROUP BY Queries

  • GROUP BY (clustering) statement indicates that it needs:
    • to divide the query results into groups;
    • apply the statistic function separately to each group and get one result record for each one.

What is the average cost of the order for every employee?

SELECT rep, AVG (amount)
FROM orders

The result is generated in such a way:

  1. If the query is the union-type (UNION, INTERSECT), it execute the steps 2 - 6 for each part of such query and get the separate results' table.
  2. To form the product of the tables listed in FROM statement. If only one table is indicated in FROM statement, it is the product itself.
  3. If there is WHERE statement, it needs apply the selection condition to each source record and leave in it the only records for which this condition is executed; that is has TRUE value; it needs ignore the records for which the selection condition has FALSE or NULL value.
  4. If there is GROUP BY statement it needs divide the records left in the product table into groups in such a way that records in every group have the same values in all the fields of clustering.
  5. For each of the left records (or for each record group) it needs compute the value of each element in the returning fields' list and create one record to the query results' table. With any field-pointer it needs take the field value for the current record (or the record group). As a statistic function argument you can apply the field value out of all fields included into group if GROUP BY statement is indicated; in another case it needs use the field values out of all records of result table.
  6. If DISTINCT keyword is presented it needs suppress all the repetition records out of the query results' table.
  7. If the query has union-type (UNION, INTERSECT:), it needs perform given set-operation on the results of execution of separate sub-queries and getting single result table. It needs suppress the repetition records out of this table if ALL keyword is absent.
  8. If there is ORDER BY statement it needs sort the query results.