Chapter 33 – Searching with Groups¶

GROUP BY Clause¶

The GROUP BY clause is an optional portion of the SELECT statement. It defines a grouped Table. The required syntax for the GROUP BY clause is:

GROUP BY <grouping specification>

   <grouping specification> ::=
   <grouping Column reference list> |
   ROLLUP (<grouping Column reference list>) |
   CUBE (<grouping Column reference list>) |
   GROUPING SETS (<grouping set list>) |
   () |
   <grouping set>,<grouping set list>

      <grouping Column reference list> ::=
      <Column reference> [ COLLATE <Collation name> ] [ ,... ]

      <grouping set list> ::=
      <grouping set> [ {,<grouping set>}... ]

         <grouping set> ::=
         <grouping Column reference> |
         (<grouping column reference list>) |
         ROLLUP (<grouping Column reference list>) |
         CUBE (<grouping Column reference list>) |
         ()

GROUP BY defines a grouped Table: a set of groups of rows, where each group consists of the rows in which all the values of the grouping Column(s) are equal (the group is the entire Table if you use the HAVING clause without a preceding GROUP BY clause). Here are three SELECT statements; each contains a GROUP BY clause:

SELECT   column_1 FROM Table_1
GROUP BY column_1;

SELECT   column_1 FROM Table_1
WHERE    column_1 BETWEEN 10 AND 50
GROUP BY column_1;

SELECT   column_1 FROM Table_1
WHERE    column_1 BETWEEN 10 AND 50
GROUP BY column_1 HAVING SUM(column_1)>12;

These examples illustrate that the GROUP BY clause contains a list of <Column reference>s (the grouping Columns) and that it comes at a certain place within the various optional or compulsory clauses of a SELECT statement. In each case, the grouping <Column reference> names a Column that belongs to a Table named in the SELECT ... FROM clause – the argument of the GROUP BY clause is a list of (optionally qualified) <Column name>s and only <Column name>s; SQL doesn’t allow you to use <literal>s, Column expressions, or any operator/function except COLLATE in a GROUP BY clause. In addition, when a SELECT statement includes GROUP BY, the statement’s select list may consist only of references to Columns that are single-valued per group – this means that the select list can’t include a reference to an interim result Column that isn’t also included in the GROUP BY clause unless that Column is an argument for one of the set functions (AVG, COUNT, MAX, MIN, SUM, EVERY, ANY, SOME; each of which reduce the collection of values from a Column to a single value). These are severe restrictions, but we’ll show you ways to get around some of the restrictions.

The GROUP BY clause allows you to summarize SQL-data. For an example of how it works, let’s look at some basic queries on one of the sample Tables we defined in our chapter on “Simple Search Conditions”: the PAYROLL Table, which looks like this:

The simplest GROUP BY example on PAYROLL groups the LOCATION Column into its four different values. Here’s the SELECT statement:

SELECT   location FROM Payroll
GROUP BY location;

To get the result for this SELECT statement, your DBMS will first evaluate the FROM clause to construct an interim result Table (in this case, the entire PAYROLL Table), then pass this interim result to the GROUP BY clause. When it evaluates the GROUP BY clause, it breaks the interim result into groups which have the same values in LOCATION (the grouping Column), then passes this interim result to the select list. When it evaluates the select list, your DBMS throws out all Columns which aren’t named in the select list. The result, then, is:

A slightly more complicated example groups PAYROLL’s LOCATION and RATE Columns. Here’s the SELECT statement:

SELECT   location, rate FROM Payroll
GROUP BY location, rate;

To get the result for this SELECT statement, your DBMS will follow the same steps we described for the last example – up until it reaches the interim result that contains groups which have the same values in LOCATION (the first grouping Column). At this point, instead of passing the result to the select list, the DBMS will now break the new interim result into groups which have the same values in LOCATION and RATE (the second grouping Column). This interim result is then passed on to the select list for evaluation. The final result is (note that the NULL value in the RATE Column is in a group of its own; for GROUP BY all NULLs form a single group):

One last example: grouping with a WHERE clause. Here’s a SELECT statement:

SELECT   location, rate FROM Payroll
WHERE    rate > 6.00
GROUP BY location, rate;

To get the result for this SELECT statement, your DBMS will get a copy of the PAYROLL Table (evaluate the FROM clause), remove any rows where the RATE value is less than or equal to 6 (evaluate the WHERE clause), break the result into groups which have the same values in LOCATION and RATE (evaluate the GROUP BY clause) and remove any groups which aren’t named in the select list. The result is:

SELECT a FROM T GROUP BY a;
   -- legal: grouping Column = select Column

SELECT a || a FROM T GROUP BY a;
   -- legal: a is grouped and it applies to both instances in select list

SELECT MAX(a) AS b FROM T GROUP BY a;
   -- legal: a need not be in select list

SELECT a+5 FROM T GROUP BY a;
   -- legal: expression in select list refers to grouping Column

SELECT 5 FROM T GROUP BY a;
   -- legal: the <literal> isn't a reference to a Column of T, so it
doesn't have to be a grouping Column: you'll get a bunch of "5"s

SELECT a*5 AS b FROM T GROUP BY b;
   -- illegal: a is not a grouping Column and b isn't evaluated until the
select list is; by then it's too late

SELECT a,max(a) FROM T;
   -- illegal: GROUP BY "implied", see set functions

SELECT a+5 FROM T GROUP BY a+5;
   -- illegal: expression in GROUP BY

SELECT    city, country FROM Cities_And_Countries
GROUP BY country;

SELECT   capitalcity, country FROM Cities_And_Countries
GROUP BY country;

SELECT   capitalcity, country FROM Cities_And_Countries
GROUP BY country, capitalcity;

SELECT   EXTRACT(MONTH FROM bdate) FROM Table_1
GROUP BY EXTRACT(MONTH FROM bdate);

SELECT   EXTRACT(MONTH FROM bdate) AS ebdate FROM Table_1
GROUP BY ebdate;

SELECT   ebdate
FROM     (SELECT EXTRACT(MONTH FROM bdate) AS ebdate FROM Table_1)
GROUP BY ebdate;

GROUP BY ROLLUP(grouping Columns)
GROUP BY CUBE(grouping Columns)
GROUP BY ()
GROUP BY GROUPING SETS grouping Column
GROUP BY GROUPING SETS (grouping Columns)
GROUP BY GROUPING SETS ROLLUP(grouping Columns)
GROUP BY GROUPING SETS CUBE(grouping Columns)
GROUP BY GROUPING SETS()
GROUP BY (grouping Columns),<grouping set list>
GROUP BY ROLLUP(grouping Columns),<grouping set list>
GROUP BY CUBE(grouping Columns),<grouping set list>
GROUP BY (),<grouping set list>

SELECT    A.id,B.name,COUNT(*)
FROM     Table_1 AS A, Table_2 as B
WHERE    A.number = B.number
GROUP BY GROUPING SETS (A.id,(A.id,B.name));

SELECT select list FROM Tables
WHERE  conditions
HAVING conditions

SELECT   select list FROM Tables
WHERE    conditions
GROUP BY GROUPING SETS (
  (ROLLUP col_1,ROLLUP col_2,...,ROLLUP col_n),
  (ROLLUP col_1,ROLLUP col_2,...,ROLLUP col_n-1),
  (ROLLUP col_1,ROLLUP col_2,...,ROLLUP col_n-2),
   ...
  (ROLLUP col_1),
  () )
HAVING   conditions

SELECT   select list FROM Tables
WHERE    conditions
GROUP BY GROUPING SETS (
  (CUBE col_1,CUBE col_2,...,CUBE col_n-2,CUBE col_n-1,CUBE col_n),
  (CUBE col_1,CUBE col_2,...,CUBE col_n-2,CUBE col_n-1),
  (CUBE col_1,CUBE col_2,...,CUBE col_n-2,CUBE col_n),
  (CUBE col_1,CUBE col_2,...,CUBE col_n-2),
  ...
  (CUBE col_1),
  (CUBE col_2,...,CUBE col_n-2,CUBE col_n-1,CUBE col_n),
  (CUBE col_2,...,CUBE col_n-2,CUBE col_n-1),
  (CUBE col_2,...,CUBE col_n-2,CUBE col_n),
  (CUBE col_2,...,CUBE col_n-2),
  ...
  (CUBE col_2),
  ...
  (CUBE col_3),
  ...
  (CUBE col_n),
  () )
HAVING   conditions

SELECT   column_1,
         column_2,
         SUM(column_3) AS "SUM"
FROM     Table_1
GROUP BY column_1,column_2;

SELECT   column_1,
         column_2,
         SUM(column_3) AS "SUM"
FROM     Table_1
GROUP BY ROLLUP(column_1,column_2);

SELECT   column_1,
         column_2,
         SUM(column_3) AS "SUM"
FROM     Table_1
GROUP BY CUBE(column_1,column_2);

Set Functions¶

The SQL set functions – more commonly called aggregate functions – are AVG, COUNT, MAX, MIN, SUM, EVERY, ANY, SOME. Each one takes the collection of values from a Column and reduces the collection to a single value. The required syntax for a set function specification is:

<set function specification> ::=
<general set function> |
COUNT(*) |
<grouping operation>

      <general set function> ::=
      <set function type> ([ {DISTINCT | ALL} ] Column expression)

         <set function type> ::=
         COUNT | MAX | MIN | SUM | AVG | EVERY | ANY | SOME

      <grouping operation> ::=
      GROUPING (<Column reference>)

The Column expression that follows a <general set function> can be a <Column reference>, a <literal>, a scalar function or an arithmetic expression – in short, it can be any expression (other than a query or subquery or another set function) which evaluates to a Column, as long as that Column doesn’t have a <data type> of BLOB, CLOB or NCLOB. Each <general set function> operates on the entire collection of non-null values in its Column argument – grouping rules apply because grouping is implied – and can optionally be qualified with either DISTINCT or ALL. If you specify DISTINCT, your DBMS will eliminate any duplicate values from the Column before applying the set function. If you specify ALL, your DBMS will apply the set function to every non-null value in the Column. The default is ALL. When a set function eliminates NULLs, your DBMS will return the SQLSTATE warning 01003 "warning-null value eliminated in set function."

Here’s an example Table; we’ll use it for the explanations of the set functions that follow:

The COUNT function has two forms: it can have an asterisk as an argument or a Column expression as an argument. It returns an integer.

• COUNT(*) returns the number of rows in the argument Table, rather than the number of values in any particular Column, so this SQL statement returns 6:

  SELECT COUNT(*) FROM Table_1
  

• COUNT(Column) and COUNT(ALL Column) are equivalent: both return the number of non-null values in “Column”, so these SQL statements both return 5:

  SELECT COUNT(column_1) FROM Table_1;
  SELECT COUNT(ALL column_1) FROM Table_1;
  

• COUNT(DISTINCT Column) returns the number of unique, non-null values in “Column”, so this SQL statement returns 3:

  SELECT COUNT(DISTINCT column_1) FROM Table_1;
  

The MAX function returns the maximum value and can’t be used with Columns that have a <data type> of ROW, REF, BLOB, CLOB or NCLOB, or with a UDT. The <data type> and size of the result will be the same as the <data type> and size of the expression itself.

• MAX(Column), MAX(ALL Column) and MAX(DISTINCT Column) are equivalent: all three return the largest of the non-null values in “Column”, so these SQL statements all return 30:

  SELECT MAX(column_1) FROM Table_1;
  SELECT MAX(ALL column_1) FROM Table_1;
  SELECT MAX(DISTINCT column_1) FROM Table_1;
  

It’s rare to find explicit ALL or DISTINCT with MAX.

The MIN function returns the minimum value and can’t be used with Columns that have a <data type> of ROW, REF, BLOB, CLOB or NCLOB, or with a UDT. The <data type> and size of the result will be the same as the <data type> and size of the expression itself.

• MIN(Column), MIN(ALL Column) and MIN(DISTINCT Column) are equivalent: all three return the smallest of the non-null values in “Column”, so these SQL statements all return 10:

  SELECT MIN(column_1) FROM Table_1;
  SELECT MIN(ALL column_1) FROM Table_1;
  SELECT MIN(DISTINCT column_1) FROM Table_1;
  

As with MAX, it’s rare to find explicit ALL or DISTINCT with MIN.

The SUM function returns a total and can’t be used with Columns that have a <data type> of ROW, REF, BLOB, CLOB or NCLOB, or with a UDT. The <data type> of the expression must be numeric or INTERVAL; in the first case, the result <data type> will also be numeric, with the same scale as the expression, but (possibly) a larger precision. That’s necessary – consider what would happen if you had a DECIMAL(2) Column containing two values: 51, 51. The result of SUM would be 102, which is DECIMAL(3), so the DBMS has to be able to increase the precision for the result. If you’re not sure that your DBMS will give SUM a great enough precision, increase the precision of the expression using a CAST function. In the second case, the result <data type> will be INTERVAL with the same precision as the expression.

• SUM(Column) and SUM(ALL Column) are equivalent: both return the total of the non-null values in “Column”, so these SQL statements both return 90:

  SELECT SUM(column_1) FROM Table_1;
  SELECT SUM(ALL column_1) FROM Table_1;
  

• SUM(DISTINCT Column) returns the total of the unique, non-null values in “Column”, so this SQL statement returns 60:

  SELECT SUM(DISTINCT column_1) FROM Table_1;
  

The AVG function returns an average and can’t be used with Columns that have a <data type> of ROW, REF, BLOB, CLOB or NCLOB, or with a UDT. The <data type> of the expression must be numeric or INTERVAL; in the first case, the result <data type> will also be numeric, with scale and precision equal to or greater than the expression’s scale and precision (most DBMSs increase the scale but leave the precision alone). In the second case, the result <data type> will be INTERVAL with the same precision as the expression.

• AVG(Column) and AVG(ALL Column) are equivalent: both return the average of the non-null values in “Column”, so these SQL statements both return 18:

  SELECT AVG(column_1) FROM Table_1;
  SELECT AVG(ALL column_1) FROM Table_1;
  

• AVG(DISTINCT Column) returns the average of the unique, non-null values in “Column”, so this SQL statement returns 20:

  SELECT AVG(DISTINCT column_1) FROM Table_1;
  

The EVERY function, new to SQL with SQL3, returns a truth value and can only be used with Columns that have a <data type> of BOOLEAN.

• EVERY(Column), EVERY(ALL Column) and EVERY(DISTINCT Column) are equivalent: all three return FALSE if any of the values in “Column” are FALSE and all three return TRUE if no value in “Column” is FALSE. For these two Tables:

  TABLE_1	TABLE_2
  COLUMN_1	COLUMN_1
  TRUE	TRUE
  TRUE	TRUE
  FALSE	UNKNOWN
  FALSE
  UNKNOWN

  these SQL statements all return FALSE:

  SELECT EVERY(column_1) FROM Table_1;
  SELECT EVERY(ALL column_1) FROM Table_1;
  SELECT EVERY(DISTINCT column_1) FROM Table_1;
  

  And these SQL statements all return TRUE:

  SELECT EVERY(column_1) FROM Table_2;
  SELECT EVERY(ALL column_1) FROM Table_2;
  SELECT EVERY(DISTINCT column_1) FROM Table_2;
  

The ANY function (and its, synonym, SOME), new to SQL with SQL3, returns a truth value and can only be used with Columns that have a <data type> of BOOLEAN.

• ANY(Column), ANY(ALL Column) and ANY(DISTINCT Column) are equivalent: all three return TRUE if any of the values in “Column” are TRUE and all three return FALSE if no value in “Column” is TRUE. For these two Tables:

  TABLE_1	TABLE_2`
  COLUMN_1	COLUMN_1
  TRUE	FALSE
  TRUE	FALSE
  FALSE
  FALSE	UNKNOWN
  UNKNOWN

  these SQL statements all return TRUE:

  SELECT ANY(column_1) FROM Table_1;
  SELECT ANY(ALL column_1) FROM Table_1;
  SELECT ANY(DISTINCT column_1) FROM Table_1;
  

  And these SQL statements all return FALSE:

  SELECT ANY(column_1) FROM Table_2;
  SELECT ANY(ALL column_1) FROM Table_2;
  SELECT ANY(DISTINCT column_1) FROM Table_2;
  

The GROUPING function, new to SQL with SQL3, operates on an argument that must be a <Column reference>, where the Column referred to is a grouping Column for the query. You use it in conjunction with the various grouping sets options of the GROUP BY clause. It returns either an integer or the value of some Column – the Standard is unclear on this point.

... WHERE <value> <comparison-operator> (SELECT <set function> ...)

SELECT ... FROM  ... WHERE ... = (SELECT MIN(price) FROM book);

SELECT * FROM Table_1 WHERE 0 = (
   SELECT SUM(Table_1.column_1+Table_2.column_1) FROM Table_2);

SELECT COUNT(*) AS pay_count
FROM   Payroll;

SELECT COUNT(rate) AS pay_count
FROM   Payroll;

SELECT COUNT(DISTINCT rate) AS pay_count
FROM   Payroll;

SELECT   location,
         SUM(rate) AS sum_rate
FROM     Payroll
GROUP BY location;

SELECT   COUNT(empnum) AS emp_count,
         dept
FROM     Employee
WHERE    dept<'D'
GROUP BY dept;

HAVING Clause¶

What departments have four employees? In which branches is the smallest book’s size less than 40mm? Those are two questions that require comparison of an absolute value with a set function, and thus represent the commonest situations where we’d find it useful to bring in a HAVING clause. The HAVING clause is an optional portion of the SELECT statement. It, too, defines a grouped Table. The required syntax for the HAVING clause is:

HAVING <search condition>

The HAVING clause defines a grouped Table that contains only those groups for which its search condition is TRUE and usually follows a GROUP BY clause. HAVING operates on the interim result produced by the evaluation of the SELECT statement at that stage:

• If the statement is “SELECT ... FROM ... HAVING ...”, HAVING operates on the entire FROM Table, treating it as a single group. (GROUP BY is implied, but the result has no grouping Columns.)
• If the statement is “SELECT ... FROM ... WHERE ... HAVING ...”, HAVING operates on the rows of the FROM Table that are TRUE for the WHERE conditions, again, treating the interim result as a single group.
• If the statement is “SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...”, HAVING operates on the interim groups returned by GROUP BY.

The HAVING clause’s search condition may include ANDs, ORs, relational operators, scalar and arithmetic expressions, and so on – much like a WHERE clause’s search condition. The difference between a WHERE condition and a HAVING condition is implied by the clause order. The DBMS evaluates WHERE before it does the grouping; it evaluates HAVING after it does the grouping – thus, WHERE filters rows and HAVING filters groups. It’s usually more efficient to filter before grouping (because then there will be fewer rows to group), so it’s best to put most simple conditions in the WHERE clause and use HAVING only for these situations:

• If one of the condition’s operands is a set function. Since a set function is not evaluated until you group, it is impossible to use one in a WHERE clause.
• If there is a quirk in a particular DBMS’s optimizer. For one DBMS, GROUP BY a HAVING a = 7 works well because (since the grouping Column and the condition Column are the same) the two clauses can be evaluated simultaneously. For most DBMSs, putting a condition in the HAVING clause is a hint that you want to avoid using any indexes.

Operands in the HAVING clause are subject to the same restrictions as in the select list:

• Column expressions in both must be single-valued per group.
• Column references must be unambiguous.
• If a SELECT statement contains HAVING without a preceding GROUP BY clause, the select list can’t include any references to Columns belonging to a Table named in the FROM clause unless those references are used with a set function.
• If HAVING includes a subquery, it can’t include outer Column references unless those references are to grouping Columns or are used with a set function.

Let’s look closely at an SQL statement which contains a HAVING clause:

SELECT   column_1,
         COUNT(column_2)
FROM     Table_1
GROUP BY column_1
HAVING   COUNT(column_1) >= 5;

In this SQL statement, the expression COUNT(column_2) is okay because, although COLUMN_2 is not a grouping Column, it appears within a set function. Suppose that TABLE_1 looks like this:

TABLE_1 has 3 rows where COLUMN_1 is 1 and 7 rows where COLUMN_1 is 2. The COLUMN_1 = 1 group does not meet the HAVING clause’s condition – the smallest acceptable count is 5. However, the COLUMN_1 = 2 group does meet HAVING’s condition, and so the result of our SQL statement is:

Here is another way to get the same result, without using a HAVING clause:

SELECT column_1,
       c_count
FROM   (SELECT  column_1,
                COUNT(column_2) AS c_count
       FROM     Table_1
       GROUP BY column_1)
WHERE  c_count >=5;

It is always possible to eliminate a HAVING clause and use a query expression in the FROM clause instead. But most programmers prefer to stick with HAVING because it is the traditional and familiar tool for solving this sort of problem.

SELECT COUNT(a) FROM Somethings
HAVING COUNT(a) > 5;

SELECT   dept FROM Employee
GROUP BY dept HAVING COUNT(*)<2;

Views of Groups¶

It’s a straightforward exercise to make a View which is based on a grouping operation – the result is a grouped View. Here’s an example:

CREATE VIEW Employee_Groups AS
   SELECT   department_id, COUNT(employee_id) AS count_employee_id
   FROM     Employees
   GROUP BY department_id;

Now, whoever uses the EMPLOYEE_GROUPS View will see an ordinary Table with two Columns: DEPARTMENT_ID and COUNT_EMPLOYEE_ID. But the user’s view window won’t be totally transparent.

Problem 1

Consider what happens for this SQL statement:

SELECT * FROM Employee_Groups
WHERE  count_employee_id = 7 AND department_id = 'XX';

Remember (from our chapter on “Tables”) that your DBMS may try to “transform” this query into a query on the underlying Base table. The best transform is:

SELECT   department_id, COUNT(employee_id) FROM Employees
WHERE    department_id = 'XX'
GROUP BY department_id HAVING COUNT(employee_id) = 7;

Observe that one, and only one, of the conditions of the original WHERE clause has been split out and put in a new HAVING clause. Based on timings, we believe that some DBMSs don’t do this. We believe they put both conditions in the HAVING clause. That’s much less efficient.

Problem 2

Consider what happens for this SQL statement:

SELECT MAX(count_employee_id) FROM Employee_Groups;

Your DBMS might attempt to transform this into:

SELECT   department_id, MAX(COUNT(employee_id)) FROM Employees
GROUP BY department_id;

that is, into a sensible question along the lines of “which department has most employees”. But MAX(COUNT(employee_id)) is an illegal expression (you can’t nest set functions) and the result will be some error message defined by your DBMS. This is legal: the SQL Standard doesn’t demand that a DBMS should handle groups within groups, or set functions within set functions.

Dialects¶

The older (pre-1992) DBMSs had these restrictions on grouping:

• Any set functions in the HAVING clause had to appear in the select list.
• HAVING was illegal if there was no GROUP BY clause.
• Groups within Views and subqueries worked only under a full moon (or some undefined equivalent!).

Nowadays, SQL DBMSs support grouping, set functions and HAVING clauses, in a manner pretty much as we have described here, except for the new SQL3 features.

It’s also fairly easy to find DBMSs which will accept non-standard syntax. Sybase ignores the Single-Value Rule so you can use any Column you like in the select list. Oracle includes basic statistical-analysis set functions: STDDEV (standard deviation) and VARIANCE. Several DBMSs allow “GROUP BY expression” or even “GROUP BY ordinal position, as if the GROUP BY clause is analogous to the ORDER BY clause.