reduce many loosely related rows down to one

Поиск
Список
Период
Сортировка
От Bill MacArthur
Тема reduce many loosely related rows down to one
Дата
Msg-id 51A0660A.2010806@dhs-club.com
обсуждение исходный текст
Ответы Re: reduce many loosely related rows down to one  (Marc Mamin <M.Mamin@intershop.de>)
Re: reduce many loosely related rows down to one  ("Torsten Grust" <torsten.grust@gmail.com>)
Список pgsql-sql
Here is a boiled down example of a scenario which I am having a bit of difficulty solving.
This is a catchall table where all the rows are related to the "id" but are entered by different unrelated processes
thatdo not necessarily have access to the other data bits.
 

CREATE TABLE test (id INTEGER,rspid INTEGER,nspid INTEGER,cid INTEGER,iac BOOLEAN,newp SMALLINT,oldp SMALLINT,ppv
NUMERIC(7,2),tppvNUMERIC(7,2)
 
);

INSERT INTO test (id, rspid, nspid, cid, iac) VALUES (1,2,3,4,TRUE);
INSERT INTO test (id, rspid, nspid, newp) VALUES (1,2,3,100);
INSERT INTO test (id, rspid, nspid, oldp) VALUES (1,2,3,200);
INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,4100);
INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,3100);
INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,-100);
INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,250);
INSERT INTO test (id, rspid, nspid, cid) VALUES (2,7,8,4);

-- raw data now looks like this:

select * from test;
 id | rspid | nspid | cid | iac | newp | oldp |   ppv   |  tppv
----+-------+-------+-----+-----+------+------+---------+---------  1 |     2 |     3 |   4 | t   |      |      |
 |  1 |     2 |     3 |     |     |  100 |      |         |  1 |     2 |     3 |     |     |      |  200 |         |  1
|    2 |     3 |     |     |      |      |         | 4100.00  1 |     2 |     3 |     |     |      |      |         |
3100.00 1 |     2 |     3 |     |     |      |      | -100.00 |  1 |     2 |     3 |     |     |      |      |  250.00
| 2 |     7 |     8 |   4 |     |      |      |         |
 
(8 rows)

-- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row)
-- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the row
containing"iac"
 
-- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for a
givenrow as well
 
 id | rspid | nspid | cid | iac | newp | oldp |   ppv   |  tppv
----+-------+-------+-----+-----+------+------+---------+---------  1 |    2  |     3 |  4  | t   | 100  | 200  |
150.00 | 7200.00  2 |    7  |     8 |  4  |     |      |      |    0.00  |    0.00
 


I have experimented with doing the aggregates as a CTE and then joining that to various incarnations of DISTINCT and
DISTINCTON, but those do not do what I want. Trying to find the right combination of terms to get an answer from Google
hasbeen unfruitful.
 

Any ideas?

Thank you for your consideration.
Bill MacArthur



В списке pgsql-sql по дате отправления:

Предыдущее
От: David Johnston
Дата:
Сообщение: Re: Select statement with except clause
Следующее
От: Brice André
Дата:
Сообщение: DELETE...RETURNING problem with libpq