# How to create aggregate function, like sum, with an implicit DISTINCT?

We have an ERP system which allows aggregates to be used (eg `SUM(foo)`) but not with DISTINCT (eg `SUM(DISTINCT foo)`.

Is it possible to create an aggregate function (`SUM_DISTINCT`), that returns the same result as as `SUM(DISTINCT foo)`, so `SUM_DISTINCT(foo) = SUM(DISTINCT foo)`?

• What "ERP system" is that? Does it allow subqueries? – Erwin Brandstetter Mar 5 '17 at 21:23

Is it possible to create an aggregate function (`SUM_DISTINCT`), that returns the same result as as `SUM(DISTINCT foo)`, so `SUM_DISTINCT(foo) = SUM(DISTINCT foo)`?

Yes, it is possible — you need a User-defined Aggregate, such as this:

``````create or replace function f_sum_distinct (numeric[], numeric) returns numeric[]
language sql as \$\$
select \$1||\$2;
\$\$;

create or replace function f_sum_distinct_final (numeric[]) returns numeric
language sql as \$\$
select sum(v) from (select distinct unnest(\$1) v) z;
\$\$;

create aggregate sum_distinct(numeric)
( sfunc     = f_sum_distinct
,stype     = numeric[]
,finalfunc = f_sum_distinct_final
);

with w(v) as (select 2 union all select 2 union all select 3)
select sum(v) "Plain SUM", sum(distinct v) "SUM(DISTINCT)", sum_distinct(v) "SUM_DISTINCT" from w;

/*
|Plain SUM|SUM(DISTINCT)|SUM_DISTINCT|
|--------:|------------:|-----------:|
|        7|            5|           5|
*/
``````

dbfiddle here

Note though (thanks @Erwin), that performance is going to be very substantially worse than the built-in aggregates. If this matters you will have to consider writing the helper functions in C, which is much more of an undertaking.

• It should be mentioned that performance is much slower than `sum(DISTINCT foo)`. I ran a quick test on a table with 46k rows. As expected, `sum_distinct(foo)` was 1000x slower than `sum(DISTINCT foo)`. And it does not scale well, i.e. it gets worse with bigger tables. IOW: don't use it unless you positively have to. – Erwin Brandstetter Mar 5 '17 at 20:41
• Thanks @Erwin. In some cases you can use built-in functions for `sfunc` and `f_sum_distinct_final` which can be substantially faster than roll-your-own functions (or you could writhe them in C of course). I tried to search for a built-in function called `numeric_accum` but although I found some references I couldn't get it to work. – Jack says try topanswers.xyz Mar 6 '17 at 6:46

I'm not sure I understand the problem. Just to rehash...

``````>>> 1+2+3+4+5
15
``````

Now sample data.

``````CREATE TABLE foo
AS
SELECT trunc(random()*5+1) AS x
FROM generate_series(1,100);
``````

Then we `SUM(DISTINCT..)`

``````SELECT SUM(DISTINCT x)
FROM foo;
sum
-----
15
``````

If as @JackDouglas suggests you want your own aggregate with distinct, just create the aggregate, from the docs

Aggregate function calls in SQL allow `DISTINCT` and `ORDER BY` options that control which rows are fed to the aggregate's transition function and in what order. These options are implemented behind the scenes and are not the concern of the aggregate's support functions.

I just don't see what this has to do with `DISTINCT`

If you want to know how to impliment your own `sum()` it's almost in the docs exactly (over the `complex` type)

``````CREATE AGGREGATE mysum (numeric)
(
stype = numeric,
initcond = '0'
);

SELECT mysum(x)
FROM ( VALUES (1),(5),(8),(8),(8) )
AS t(x);

SELECT mysum(DISTINCT x)
FROM ( VALUES (1),(5),(8),(8),(8) )
AS t(x);
``````

As a special note `numeric_add(numeric,numeric)` is an undocumented internal function. It's used by the `+` operator, but you can put anything you want there that takes two `numeric`.