Re: Using distinct with sum()

[Christoph della Valle <christoph(dot)dellavalle(at)goetheanum(dot)ch>]

hi

maybe not elegant, but it works:

SELECT count(id) as id,
              sum(total) as total
from (SELECT DISTINCT count(id),sum(value2 - value1) AS total FROM t1) 
as t2;

Yours,
Christoph

A Gilmore schrieb:
> Hello,
>
> I've trying to sum up distinct columns.  But it's summing them prior 
> to the distinct taking effect.
>
> --
> CREATE TABLE t1 (
>     id        integer,
>     value1        integer,
>     value2        integer,
>     value3        integer
>     );
>
> INSERT INTO t1 (id,value1,value2,value3) VALUES (1,100,300,null);
> INSERT INTO t1 (id,value1,value2,value3) VALUES (1,100,300,1);
> INSERT INTO t1 (id,value1,value2,value3) VALUES (1,100,300,2);
> INSERT INTO t1 (id,value1,value2,value3) VALUES (2,200,400,3);
> INSERT INTO t1 (id,value1,value2,value3) VALUES (3,400,500,4);
>
> SELECT DISTINCT id,value2 - value1 AS total FROM t1;
>
>  id | total
> ----+-------
>   1 |   200
>   2 |   200
>   3 |   100
> --
>
> Now what I'd like to do count(id) and sum(total)
>
> --
> SELECT DISTINCT count(id),sum(value2 - value1) AS total FROM t1;
>
>  count | total
> -------+-------
>      5 |   900
> --
>
> I can get count() working how like by doing count(DISTINCT id) but how 
> do I do get a query to produce the following output (count/sum 
> post-distinct):
>
>  count | total
> -------+-------
>      3 |   500
>
> Thank you in advance,
> - A Gilmore
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq