From: | A Gilmore <agilmore(at)shaw(dot)ca> |
---|---|
To: | Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Using distinct with sum() |
Date: | 2007-02-20 01:08:08 |
Message-ID: | 45DA49F8.3010901@shaw.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John Gardner | 2007-02-20 14:27:16 | Questions from a Newbie |
Previous Message | Sean Davis | 2007-02-19 13:58:16 | Re: Question about Performance of my db design |