Thread: Re: SQL

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/

> Creating the temp table is the easy part ... ;-)
>
> create temp table mytable (...);

If I create a temp table and destroy it a few seconds later I understand
that it is specific to each individual user.  Is there a danger if the
user is apache and two people can be asking apache to run the same script
at the same time?  Is that two users or two instances of the same user
that could collide?

brew

I've looked in a bunch of places (include the describe.c as suggested
in the FAQ; I'm just really rusty on my C and as such got nothing from
the code) on how to 'describe' a table.  In MySQL, I could simply use
the command:

desc tablename;

What is the command in pgsql?
I apologize for the stupid question but I've spent the last several hours
looking for the stupid answer. :P

thnx,
Chris

\d tablename

should do the trick.

take care,
-tuna

On Wed, 2002-11-13 at 11:55, Chris Boget wrote:
> I've looked in a bunch of places (include the describe.c as suggested
> in the FAQ; I'm just really rusty on my C and as such got nothing from
> the code) on how to 'describe' a table.  In MySQL, I could simply use
> the command:
>
> desc tablename;
>
> What is the command in pgsql?
> I apologize for the stupid question but I've spent the last several hours
> looking for the stupid answer. :P
>
> thnx,
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Hi,

>I've looked in a bunch of places (include the describe.c as suggested
>in the FAQ; I'm just really rusty on my C and as such got nothing from
>the code) on how to 'describe' a table.  In MySQL, I could simply use
>the command:
>
>desc tablename;

Try \d tablename

If you want to see the query this actually runs, connect to postgres with
the -E option:

psql -E database

HTH,

Chris Smith

 >> 92 Jarrett St Leichhardt, Sydney, NSW 2040 ...>
T: + 61 2 9568 6866
F: + 61 2 9568 6733
W: http://www.squiz.net/
.....>> Open Source - Own it - Squiz.net ...../>