Skip site navigation (1) Skip section navigation (2)

unsure of how to query for desired data/output

From: Carol Cheung <cacheung(at)consumercontact(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: unsure of how to query for desired data/output
Date: 2008-12-01 16:35:09
Message-ID: 4934123D.5040109@consumercontact.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.




Responses

pgsql-novice by date

Next:From: Andreas JuniusDate: 2008-12-01 17:27:18
Subject: SQL Status:42883
Previous:From: Bruce HyattDate: 2008-12-01 03:27:33
Subject: Re: Postgresql Books

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group