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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Evert Carton | 2002-11-12 07:45:37 | Re: SQL |
Previous Message | cristi | 2002-11-12 06:45:37 | SQL |