IBM PureData System for Analytics, Version 7.1

Grouped aggregates

Grand grouped aggregates return a single result for all the data. For example, the following query shows a grand grouped aggregate; it returns the highest low-temperature reading in the table weather:

MYDB.SCHEMA(USER)=> SELECT max(temp_lo) FROM weather;
 MAX
-----
   55

As shown in this example, the grouped aggregates provide a summary (in this case, the maximum value) of a set of rows, but they do not preserve any detail of the information. They return group column and aggregate values.

When aggregates are used in combination with the GROUP BY clause, the query returns a result for each group. For example, the following query displays the maximum low temperature observed in each city in the table:

MYDB.SCHEMA(USER)=> SELECT city, max(temp_lo) FROM weather GROUP BY city;
     CITY      | MAX
---------------+-----
 San Francisco |  55
 Boston        |  40
(2 rows)

The system computes an aggregate for each city. You can filter these grouped rows by using HAVING:

MYDB.SCHEMA(USER)=> SELECT city, max(temp_lo) FROM weather GROUP BY city
HAVING max(temp_lo) < 42;
  CITY  | MAX
--------+-----
 Boston |  40
(1 row)

The following example uses a subquery to identify the city or cities where the max temp_lo value occurred. The subquery is necessary because the aggregate max cannot be used in a WHERE clause.

MYDB.SCHEMA(USER)=> SELECT city FROM weather WHERE temp_lo = (SELECT
max(temp_lo) FROM weather);
      CITY
---------------
 San Francisco
(1 row)

For more information about the available aggregate functions, see Aggregate functions.

Netezza® supports the enhanced SQL GROUP BY clause syntax of rollup, cube, and grouping set for user tables.

Note: To avoid unwanted ordering of results, control the order by using an explicit ORDER BY. You might also need to include the GROUPING function, if nulls are in the data.

Note: Currently, the Netezza system does not support the use of CUBE, ROLLUP, or GROUPING SET syntax with hypothetical set functions or inverse distribution functions. Doing so results in an error, as in the following example:

SELECT percentile_disc (10) WITHIN GROUP ( order by col1_Srno) FROM
advanced_analytic_1 GROUP BY CUBE(col1_int1,col1_int2);
ERROR: CUBE, ROLLUP, GROUPING SETS, GROUPING not permitted with WITHIN
GROUP