Using distinct with sum()

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

Предыдущее
От: Sean Davis
Дата:
Сообщение: Re: Question about Performance of my db design
Следующее
От: John Gardner
Дата:
Сообщение: Questions from a Newbie