Re: SQL

This is actually pretty hard.  The best I could come up with is

select id, A = case name when 'A' then count(*) else 0 end,
B = case name when 'B' then count(*) else 0 end,
C = case name when 'C' then count(*) else 0 end
from test
group by id, name



which doesn't really do what you want.  I'm sure you could work out
something eventually, but that would probably take a long time.  My
suggestion would be to actually create a temp table and use that instead.
Something like

Create table temp_a(marca int4,
            condceddi int4,
            boala int4,
            obligatii int4)

insert into temp_a (marca)
select distinct marca
from a

update temp_a set condceddi = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'A')

update temp_a set boaloa = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'B')

update temp_a set obligatti = (select count(ma) from a as a
where a.id = temp_a.id
and name like 'C')

select * from temp_a

and then drop the table (or, better yet, create a temp table - but I'm not
sure how to do this in postgres).

If anyone knows a better way, I would be most interested to know.


---
Rob

**************************
Rob Cherry
mailto:rob@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of cristi
> Sent: 12 November 2002 08:46
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] SQL
>
>
> I have a table a:
> create tabel a( marca int4,
>                        mo varchar(1)
>                     );
>
> This is the contents of the table:
> marca     mo
> 1            C
> 2            C
> 4            B
> 5            O
> 1            C
> 1            B
>
> I need a SQL interogation with following results:
>
> marca concedii boala obligatii
>   1         2           1        0
>   2         1            0       0
>   4         0            1       0
>   5         0            0       1
>
> How can I do that?
>
>
> Thanks!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>