Re: Create a table B with data coming from table A

am Mon, dem 11.06.2007, um 21:23:59 -0000 mailte lhaj(dot)merigh(at)gmail(dot)com 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