The GROUP BY Clause

  • The GROUP BY clause is intimately connected to aggregates.
  • The clause divides a table into a sets of rows, and aggregate functions produce summary values for each set-group.
  • These values are called vector aggregates.
    GROUP BY syntax included in the SELECT statement:
    SELECT  select_list
      FROM table/view_list
      [WHERE search_conditions]
      [GROUP BY group_by_list]
      [ORDER BY order_by_list ]
  • Just as you can sort multiple items, you can form groups within groups.
  • You separate the grouping elements with commas, and go from large groups to progressively smaller ones.
  • GROUP BY parts tables in a set of rows, but not necessarily sort them in an orderly sequence.
    Here is an example of using one item in the GROUP BY clause:
    MySQL, Oracle, SQL server, PostgreSQL:
    select publisher, min(price)  "Low Price",
                      max(price) as "High price",
                      count(*) "Numbers"
      from bookstore
      group by publisher
      order by  publisher, count(*);
    The result should be:
    publisher Low Price High price Numbers
    Addison Wesley 39 39 1
    Apress 1
    OReilly 32 32 1
    Sams Publishing 49 49 1
    Wrox 29 42 5
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.