Functions supported for live aggregate projections
Vertica can aggregate results in live aggregate projections from the following aggregate functions:.
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 nonconstant.

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
(
customer_region,
annual_income,
Count
)
AS
SELECT customer_dimension.customer_region,
customer_dimension.annual_income,
count(*) AS Count
FROM public.customer_dimension
GROUP BY customer_dimension.customer_region,
customer_dimension.annual_income
;
Note
This projection includes the aggregate functionCOUNT
, which here serves no logical objective; it is included only because live aggregate projections require at least one aggregate function.