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)
fooon 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