FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

HAVING

Just as a WHERE clause limits the rows in a dataset to those that meet the search condition, so the HAVING sub-clause imposes restrictions on the aggregated rows in a grouped set.HAVING is optional, and can only be used in conjunction with GROUP BY.

The condition(s) in the HAVING clause can refer to:

  • Any aggregated column in the select list.This is the most widely used case.

  • Any aggregated expression that is not in the select list, but allowed in the context of the query.This is sometimes useful too.

  • Any column in the GROUP BY list.While legal, it is more efficient to filter on these non-aggregated data at an earlier stage: in the WHERE clause.

  • Any expression whose value doesn’t depend on the contents of the dataset (like a constant or a context variable).This is valid but not useful, because it will either suppress the entire set or leave it untouched, based on conditions that have nothing to do with the set itself.

A HAVING clause can not contain:

  • Non-aggregated column expressions that are not in the GROUP BY list.

  • Column positions.An integer in the HAVING clause is just an integer, not a column position.

  • Column aliases –- not even if they appear in the GROUP BY clause!

Examples

Building on our earlier examples, this could be used to skip small groups of students:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having count(*) >= 5;

To select only groups that have a minimum age spread:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having max(age) - min(age) > 1.2;

Notice that if you’re interested in this information, you’ll likely also include min(age) and max(age) — or the expression “max(age) - min(age)”.

To include only 3rd classes:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having class starting with '3';

Better would be to move this condition to the WHERE clause:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M' and class starting with '3'
  group by class;

The WINDOW Clause

The WINDOW clause defines one or more named windows that can be referenced by window functions in the current query specification.

Syntax
<query_spec> ::=
  SELECT
    [<limit_clause>]
    [<distinct_clause>]
    <select_list>
    <from_clause>
    [<where_clause>]
    [<group_clause>]
    [<having_clause>]
    [<named_windows_clause>]
    [<plan_clause>]

<named_windows_clause> ::=
  WINDOW <window_definition> [, <window_definition> ...]

<window definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  !! See Window (Analytical) Functions !!

In a query with multiple SELECT and WINDOW clauses (for example, with subqueries), the scope of the `new_window_name_ is confined to its query context.That means a window name from an inner context cannot be used in an outer context, nor vice versa.However, the same window name can be used independently in different contexts, though to avoid confusion it might be better to avoid this.

For more information, see [fblangref50-windowfuncs].

Example Using Named Windows
select
  id,
  department,
  salary,
  count(*) over w1,
  first_value(salary) over w2,
  last_value(salary) over w2
from employee
window w1 as (partition by department),
       w2 as (w1 order by salary)
order by department, salary;