Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Using distinct with sum()



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



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group