Re: unsure of how to query for desired data/output

On Mon, Dec 1, 2008 at 11:35 AM, Carol Cheung
<cacheung(at)consumercontact(dot)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)