Single DISTINCT aggregates

Vertica computes a DISTINCT aggregate by first removing all duplicate values of the aggregate's argument to find the distinct values. Then it computes the aggregate.

For example, you can rewrite the following query:

SELECT a, b, COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a, b;

as:

SELECT a, b, COUNT(dcnt) FROM
  (SELECT a, b, c AS dcnt FROM table1 GROUP BY a, b, c)
GROUP BY a, b;

For fastest execution, apply the optimization techniques for GROUP BY queries.