am Mon, dem 11.06.2007, um 21:23:59 -0000 mailte lhaj.merigh@xxxxxxxxx folgendes: > My original table is like that: > > ID A1 A2 A3 cnt > 1234 1 0 0 4 > 1234 1 0 1 8 > 1234 1 1 1 5 > 1235 1 0 0 6 > 1235 1 0 1 7 > 1235 1 1 1 12 > > I have to create a new table B: > > ID B1 B2 B3 S > 1234 4 8 5 17 > 1235 6 7 12 25 > > The combination (A1=1,A2=0,A3=0) gives B1 > The combination (A1=1,A2=0,A3=0) gives B2 > The combination (A1=1,A2=1,A3=1) gives B3 > > S = B1+B2+B3 > > I think it's a classical problem, but i can't see to problem key test=*# select * from tab_a; id | a1 | a2 | a3 | cnt ------+----+----+----+----- 1234 | 1 | 0 | 0 | 4 1234 | 1 | 0 | 1 | 8 1234 | 1 | 1 | 1 | 5 1235 | 1 | 0 | 0 | 6 1235 | 1 | 0 | 1 | 7 1235 | 1 | 1 | 1 | 12 (6 rows) test=*# select id, b1,b2,b3, sum(b1+b2+b3) as s from (select id, sum(case when a1=1 and a2=0 and a3=0 then cnt else 0 end) as b1, sum(case when a1=1 and a2=0 and a3=1 then cnt else 0 end) as b2, sum(case when a1=1 and a2=1 and a3=1 then cnt else 0 end) as b3 from tab_a group by id order by id) foo group by id, b1, b2, b3 order by id; id | b1 | b2 | b3 | s ------+----+----+----+---- 1234 | 4 | 8 | 5 | 17 1235 | 6 | 7 | 12 | 25 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
