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

Re: SQL

From: "Rob" <rob(at)jamwarehouse(dot)com>
To: "cristi" <cristi(at)dmhi(dot)ct(dot)ro>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL
Date: 2002-11-12 07:42:30
Message-ID: MIENLCBGIJDKLHHBLCDGEEHLCCAA.rob@jamwarehouse.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>


In response to

  • SQL at 2002-11-12 06:45:37 from cristi

Responses

  • Re: SQL at 2002-11-12 09:14:09 from cristi

pgsql-novice by date

Next:From: Evert CartonDate: 2002-11-12 07:45:37
Subject: Re: SQL
Previous:From: cristiDate: 2002-11-12 06:45:37
Subject: SQL

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