Re: SQL - Mailing list pgsql-novice

Yes!
Excellent!
Thank you very very much!

> 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>