Functions Supported for Live Aggregate Projections

Vertica can aggregate results in live aggregate projections from the following aggregate functions:

Aggregate Functions with DISTINCT

Live aggregate projections can support queries that include aggregate functions qualified with the keyword DISTINCT. The following requirements apply:

  • The aggregated expression must evaluate to a non-constant.
  • The projection's GROUP BY clause must specify the aggregated expression.

For example, the following query uses SUM(DISTINCT) to calculate the total of all unique salaries in a given region:

SELECT customer_region, SUM(DISTINCT annual_income)::INT 
   FROM customer_dimension GROUP BY customer_region;

This query can use the following live aggregate projection, which specifies the aggregated column (annual_income) in its GROUP BY clause:

CREATE PROJECTION public.TotalRegionalIncome
 SELECT customer_dimension.customer_region,
        count(*) AS Count
 FROM public.customer_dimension
 GROUP BY customer_dimension.customer_region,

This projection includes the aggregate function COUNT, which here serves no logical objective; it is included only because live aggregate projections require at least one aggregate function.