From: | Rodrigo E(dot) De León Plicet <rdeleonp(at)gmail(dot)com> |
---|---|
To: | "Carol Cheung" <cacheung(at)consumercontact(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: unsure of how to query for desired data/output |
Date: | 2008-12-01 21:08:15 |
Message-ID: | a55915760812011308x5d0f0be1p85fffe2cefd50522@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Roberts | 2008-12-01 22:37:41 | Re: unsure of how to query for desired data/output |
Previous Message | Andreas Kretschmer | 2008-12-01 19:18:43 | Re: SQL Status:42883 |