Re: unsure of how to query for desired data/output - Mailing list pgsql-novice

On Mon, Dec 1, 2008 at 11:35 AM, Carol Cheung
<cacheung@consumercontact.com> wrote:
> Is it possible to get the following based on the above list of numbers:
>
> 25|3
> 3,17|8
> 40,28,6|3
> 17|2

create table t (
status int
);

insert into t values
(25),(25),(25),(3),(3),(3),(3),(3),(3),(17),(17),(6),(28),(28);

select * from t;

 status
--------
     25
     25
     25
      3
      3
      3
      3
      3
      3
     17
     17
      6
     28
     28
(14 rows)

select replace(replace(x.val::text,'{',''),'}','') as status, count(t.*)
from t, (values ('{25}'::int[]), ('{3,17}'), ('{40,28,6}'), ('{17}')) as x(val)
where t.status=any(x.val)
group by replace(replace(x.val::text,'{',''),'}','');

 status  | count
---------+-------
 25      |     3
 3,17    |     8
 40,28,6 |     3
 17      |     2
(4 rows)