Re: PostgreSQL Question

On Thu, Jul 31, 2003 at 11:52:42AM +0800, Ryan F. Bayhonan wrote:
> Hi Patrick,
>
> Have read your reply in the web about postgreSQL.
> (http://archives.postgresql.org/pgsql-interfaces/2001-01/msg00170.php)
>
> My name is Ryan, and I would like to ask some help about PostgreSQL.
>
> I have the relations below:
>
> TABLE_CLIENT:
> CID | CLIENT_NAME
> ------+--------------
> 1 | RFB CO.
> 2 | ABC CO.
>
> TABLE_PROJECTS:
> PID | PROJECT_NAME | STATUS | PID
> ------+------------------+------------+-------
> 1 | PROJECT_A | ACTIVE | 1
> 2 | PROJECT_B | ACTIVE | 1
> 3 | PROJECT_C | CLOSED | 1
> 4 | PROJECT_D | CLOSED | 1
> 5 | PROJECT_E | ACTIVE | 2
> 6 | PROJECT_F | ACTIVE | 2
>
> I want to list all the CLIENT and know how many projects ACTIVE
> and how many are CLOSED. I want to have a result shown below:
>
> CID | CLIENT_NAME | ACTIVE | CLOSED
> ------+-----------------+------------+------------
> 1 | RFB CO. | 2 | 2
> 2 | ABC CO. | 2 | 0
>
>
> What would be the correct SQL syntax for the above result?

I have no idea if this is "correct", just that it seems to work.
By the way, you are much better off posting to pgsql-sql or
pgsql-general @ postgresql.org...

Cheers,

Patrick

begin;
create table client (
id serial primary key,
"name" text
);

create table status (
id serial primary key,
"name" text
);

create table projects (
id serial primary key,
"name" text,
status integer references status,
cid integer references client
);

insert into client ("name") values ('RFB CO.');
insert into client ("name") values ('ABC CO.');

insert into status ("name") values ('ACTIVE');
insert into status ("name") values ('CLOSED');

insert into projects ("name",status,cid) values ('PROJECT_A',1,1);
insert into projects ("name",status,cid) values ('PROJECT_B',1,1);
insert into projects ("name",status,cid) values ('PROJECT_C',2,1);
insert into projects ("name",status,cid) values ('PROJECT_D',2,1);
insert into projects ("name",status,cid) values ('PROJECT_E',1,2);
insert into projects ("name",status,cid) values ('PROJECT_F',1,2);
commit;

select * from client order by id;

select p.id,p.name,s.name,p.cid
from projects as p,status as s
where p.status=s.id
order by id
;

select c.id,c.name,
coalesce(
(select count(*)
from projects as p
where p.cid=c.id
and p.status=1
group by c.id,c.name
),0) as active,
coalesce(
(select count(*)
from projects as p
where p.cid=c.id
and p.status=2
group by c.id,c.name
),0) as closed
from client as c
order by id
;