Re: SQL - Mailing list pgsql-novice

Creating the temp table is the easy part ... ;-)

create temp table mytable (...);

Evert Carton
Medical Research Laboratories
evertcarton@netscape.net
evert.carton@mrlinternational.com


"Rob" <rob@jamwarehouse.com> wrote:

>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)
>

__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/