Thread: unsure of how to query for desired data/output
Hi, I have a table with a column of integers called 'status'. A number may appear 0+ times in this column. For example, status -------- 25 28 28 ... I know I can get counts of the each status using select count(status), status from test_table group by status; Let's say I have some lists of numbers, like so: 25 3,17 40,28,6 17 25 appears in the status column 3 times. 3 appears in the status column 6 times. 17 appears in the status column 2 times. 40 appears in the status column 0 times. 6 appears in the status column 1 time. 28 appears in the status column 2 times 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 the first column is the list of status, the second column is the sum of the counts of the status codes appearing on the left of the "|" Does anyone have any ideas on how to get this output without executing 4 separate queries? Thanks.
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)
On Mon, 2008-12-01 at 16:08 -0500, Rodrigo E. De León Plicet wrote:
> 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)
>
Well, if you have a somewhat static list of interesting statuses (such
that you always want to monitor for the same set of statuses), you could
try something like this. It works by using a table
(interesting_statuses) that groups the statuses into an interesting
group with an array.
The join makes sure that each status (and sum) is joined to the
appropriate array(s), and the final group simply sums the appropriate
status counts by list.
For more information about arrays:
http://www.postgresql.org/docs/8.3/interactive/arrays.html
--- SETUP ---
create temp table status (status integer);
create temp table interesting_statuses (status_list_no integer,
status_list integer[]);
insert into status values
(25),(25),(25),(3),(3),(3),(3),(3),(3),(17),(17),(6),(28),(28);
insert into interesting_statuses values (0, ARRAY[25]), (1, ARRAY[3,
17]), (2, ARRAY[40,28,6]), (3, ARRAY[17]);
-- Query --
select status_list, sum(status_count) as total_status_counts
from (
select status, count(1) as status_count
from status
group by status
) x
left outer join interesting_statuses int on (status =
ANY(status_list))
group by status_list
order by sum(status_count) desc
;
----
CREATE TABLE
Time: 3.839 ms
CREATE TABLE
Time: 62.156 ms
INSERT 0 14
Time: 1.737 ms
INSERT 0 4
Time: 0.579 ms
status_list | total_status_counts
-------------+---------------------
{3,17} | 8
{25} | 3
{40,28,6} | 3
{17} | 2
(4 rows)
-Mark
A coworker points out that my status list can be more naturally
expressed as an unkeyed join table:
create table status_groupings (
status_no,
grouping_no
);
I also suggest starting with status_groupings and then left joining over
to statuses (so that you catch the case of a status not having any
values), or inner joining so that you cull out unnecessary statuses.
Shout outs to Kane.
-Mark
On Mon, 2008-12-01 at 14:37 -0800, Mark Roberts wrote:
> On Mon, 2008-12-01 at 16:08 -0500, Rodrigo E. De León Plicet wrote:
> > 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)
> >
>
> Well, if you have a somewhat static list of interesting statuses (such
> that you always want to monitor for the same set of statuses), you could
> try something like this. It works by using a table
> (interesting_statuses) that groups the statuses into an interesting
> group with an array.
>
> The join makes sure that each status (and sum) is joined to the
> appropriate array(s), and the final group simply sums the appropriate
> status counts by list.
>
> For more information about arrays:
> http://www.postgresql.org/docs/8.3/interactive/arrays.html
>
> --- SETUP ---
> create temp table status (status integer);
> create temp table interesting_statuses (status_list_no integer,
> status_list integer[]);
>
> insert into status values
> (25),(25),(25),(3),(3),(3),(3),(3),(3),(17),(17),(6),(28),(28);
> insert into interesting_statuses values (0, ARRAY[25]), (1, ARRAY[3,
> 17]), (2, ARRAY[40,28,6]), (3, ARRAY[17]);
>
> -- Query --
>
> select status_list, sum(status_count) as total_status_counts
> from (
> select status, count(1) as status_count
> from status
> group by status
> ) x
> left outer join interesting_statuses int on (status =
> ANY(status_list))
> group by status_list
> order by sum(status_count) desc
> ;
>
> ----
> CREATE TABLE
> Time: 3.839 ms
> CREATE TABLE
> Time: 62.156 ms
> INSERT 0 14
> Time: 1.737 ms
> INSERT 0 4
> Time: 0.579 ms
> status_list | total_status_counts
> -------------+---------------------
> {3,17} | 8
> {25} | 3
> {40,28,6} | 3
> {17} | 2
> (4 rows)
>
>
> -Mark
>
>