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
;