<prompt>=&gt;</prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) &gt; 3;</userinput>
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

<prompt>=&gt;</prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x &lt; 'c';</userinput>
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)
</screen>
   </para>

   <para>
    Again, a more realistic example:
<programlisting>
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date &gt; CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) &gt; 5000;
</programlisting>
    In the example above, the <literal>WHERE</literal> clause is selecting
    rows by a column that is not grouped (the expression is only true for
    sales during the last four weeks), while the <literal>HAVING</literal>
    clause restricts the output to groups with total gross sales over
    5000.  Note that the aggregate expressions do not necessarily need
    to be the same in all parts of the query.
   </para>

   <para>
    If a query contains aggregate function calls, but no <literal>GROUP BY</literal>
    clause, grouping still occurs: the result is a single group row (or
    perhaps no rows at all, if the single row is then eliminated by
    <literal>HAVING</literal>).
    The same is true if it contains a <literal>HAVING</literal> clause, even
    without any aggregate function calls or <literal>GROUP BY</literal> clause.
   </para>
  </sect2>

  <sect2 id="queries-grouping-sets">
   <title><literal>GROUPING SETS</literal>, <literal>CUBE</literal>, and <literal>ROLLUP</literal></title>

   <indexterm zone="queries-grouping-sets">
    <primary>GROUPING SETS</primary>
   </indexterm>
   <indexterm zone="queries-grouping-sets">
    <primary>CUBE</primary>
   </indexterm>
   <indexterm zone="queries-grouping-sets">
    <primary>ROLLUP</primary>
   </indexterm>

   <para>
    More complex grouping operations than those described above are possible
    using the concept of <firstterm>grouping sets</firstterm>.  The data selected by
    the <literal>FROM</literal> and <literal>WHERE</literal> clauses is grouped separately
    by each specified grouping set, aggregates computed for each group just as
    for simple <literal>GROUP BY</literal> clauses, and then the results returned.
    For example:
<screen>
<prompt>=&gt;</prompt> <userinput>SELECT * FROM items_sold;</userinput>
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

<prompt>=&gt;</prompt> <userinput>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</userinput>
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)
</screen>
   </para>

   <para>
    Each sublist of <literal>GROUPING SETS</literal> may specify zero or more columns
    or expressions and is interpreted the same way as though it were directly
    in the <literal>GROUP BY</literal> clause.  An empty grouping set means that all
    rows are aggregated down to a single group (which is output even if no
    input rows were present), as described above for the case of aggregate
    functions with no <literal>GROUP BY</literal> clause.
   </para>

   <para>
    References to the grouping columns or expressions are replaced
    by null values in result rows for grouping sets in which those
    columns do not appear.  To distinguish which grouping a particular output
    row resulted from, see <xref linkend="functions-grouping-table"/>.
   </para>

   <para>
    A shorthand notation is provided for specifying two common types of grouping set.
    A clause of the form
<programlisting>
ROLLUP ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... )
</programlisting>
    represents