What is the GROUP BY clause?

Document ID : KB000055835
Last Modified Date : 14/02/2018
Show Technical Document Details

A GROUP BY clause directs a query to return a single summary row for each group of similar rows. The clause is required in the following cases:

  • The query's SELECT clause includes both aggregate expressions and non-aggregate columns or expressions. IN this case, every non-aggregate column or expression listed in the SELECT statement must appear in the GROUP BY clause. If your query selects columns from multiple tables, you should be aware of the issues outline in the help topic Cross-Table Aggregations.
  • The query includes a HAVING clause that places conditions on the groups formed by the GROUP BY clause.

You can also use GROUP BY in a query without aggregate functions, in order to eliminate duplicate rows in a manner similar to SELECT DISTINCT, which F&T SQL does not support.

NOTE: Table views have a Categorize column option (see online help topic Changing View Column Layout Settings) that enables user to select columns in which duplicate values in adjacent rows are consolidated into a single value, with the extra option of subtotaling the values in each category of rows. You can also programmatically categorize Table view columns with the function View('SetColumnCategory'). A categorized view display is similar to what you would see if you had used a GROUP BY and aggregate functions in a query, but the underlying view result is still ungrouped and unaggregated - that is, a view reference to, or query of, a categorized column will still return all individual rows within each category without any subtotals and totals that appear in the view display. Also note that column categories, subtotals, and totals are calculated after a view's query has been executed.