From: | Christoph della Valle <christoph(dot)dellavalle(at)goetheanum(dot)ch> |
---|---|
To: | A Gilmore <agilmore(at)shaw(dot)ca> |
Cc: | Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Using distinct with sum() |
Date: | 2007-02-22 07:44:09 |
Message-ID: | 45DD49C9.2080606@goetheanum.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Nabil | 2007-02-23 14:14:59 | Re: |
Previous Message | Tom Lane | 2007-02-22 06:48:12 | Re: |