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)
(
sfunc = numeric_add,
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
.