Skip site navigation (1) Skip section navigation (2)

Re: SQL

From: evertcarton(at)netscape(dot)net (Evert Carton)
To: rob(at)jamwarehouse(dot)com ("Rob"), cristi(at)dmhi(dot)ct(dot)ro ("cristi"),pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL
Date: 2002-11-12 07:45:37
Message-ID: 2DCCD534.08F15C45.4B8A5395@netscape.net (view raw or flat)
Thread:
Lists: pgsql-novice
Creating the temp table is the easy part ... ;-)

create temp table mytable (...);

Evert Carton
Medical Research Laboratories
evertcarton(at)netscape(dot)net
evert(dot)carton(at)mrlinternational(dot)com


"Rob" <rob(at)jamwarehouse(dot)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(at)jamwarehouse(dot)com
>+27 21 447 7440
>Jam Warehouse RSA
>Smart Business Innovation
>http://www.jamwarehouse.com
>**************************
>
>
>> -----Original Message-----
>> From: pgsql-novice-owner(at)postgresql(dot)org
>> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of cristi
>> Sent: 12 November 2002 08:46
>> To: pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)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/

Responses

  • SQL at 2002-11-13 16:35:32 from brew

pgsql-novice by date

Next:From: cristiDate: 2002-11-12 09:14:09
Subject: Re: SQL
Previous:From: RobDate: 2002-11-12 07:42:30
Subject: Re: SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group