Using distinct with sum()
От | A Gilmore |
---|---|
Тема | Using distinct with sum() |
Дата | |
Msg-id | 45DA49F8.3010901@shaw.ca обсуждение исходный текст |
Ответы |
Re: Using distinct with sum()
|
Список | 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
В списке pgsql-novice по дате отправления: