Re: SELECT counts

First of all, please don't hijack other threads.

In response to Jayadevan M :
> Hello all,
>
> I have a table with id and status_v.
>
> create table myt (id integer, status_v varchar(10));
> insert into myt values (1,'I');
> insert into myt values (1,'P');
> insert into myt values (1,'T');
> insert into myt values (2,'P');
>
> User gets counts of status records, filtered on some status_v like this
> test=# select count(*), status_v from myt where status_v in ('I','P','X')
>
> group by status_v;
> count | status_v
> -------+----------
> 1 | I
> 2 | P
> (2 rows)
>
> Is there a way in which I can have the output as
> 1 | I
> 2 | P
> 0 | X
>
> i.e., if there is not data, the count should be displayed as 0.

test=*# select * from myt;
id | status_v
----+----------
1 | I
1 | P
1 | T
2 | P
(4 rows)

test=*# select foo.status_v, count(myt.status_v) from myt right join (select unnest(array['I','P','X']) as status_v) foo on foo.status_v=myt.status_v group by foo.status_v;
status_v | count
----------+-------
X | 0
I | 1
P | 2
(3 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99