On 02/22/2012 12:36 PM, Alexander Farber wrote:
Hello,
I have a table holding week numbers (as strings)
and user ids starting with OK, VK, FB, GG, MR, DE
(coming through diff. soc. networks to my site):
afarber@www:~> psql
psql (8.4.9)
Type "help" for help.
pref=> select * from pref_money;
id | money | yw
-------------------------+--------+---------
OK19644992852 | 8 | 2010-44
OK21807961329 | 114 | 2010-44
FB1845091917 | 774 | 2010-44
OK172682607383 | -34 | 2010-44
VK14831014 | 14 | 2010-44
VK91770810 | 2368 | 2010-44
DE8341 | 795 | 2010-44
VK99736508 | 97 | 2010-44
I'm trying to count those different users.
For one type of users (here Facebook) it's easy:
pref=> select yw, count(*) from pref_money
where id like 'FB%' group by yw order by yw desc;
yw | count
---------+-------
2012-08 | 32
2012-07 | 32
2012-06 | 37
2012-05 | 46
2012-04 | 41
But if I want to have a table displaying all users
(a column for "FB%", a column for "OK%", etc.) -
then I either have to perform a lot of copy-paste and
vim-editing or maybe someone can give me an advice?
I've reread the having-doc at
http://www.postgresql.org/docs/8.4/static/tutorial-agg.html
and still can't figure it out...
How about?:
test=> \d storage_test
Table "public.storage_test"
Column | Type | Modifiers
---------+-----------------------+-----------
fld_1 | character varying |
fld_2 | character varying(10) |
fld_3 | character(5) |
fld_int | integer
test=> SELECT * from storage_test ;
fld_1 | fld_2 | fld_3 | fld_int
-------+-------+-------+---------
FB001 | one | | 4
FB002 | three | | 10
OK001 | three | | 5
OK002 | two | | 6
VK001 | one | | 9
VK002 | four | | 2
test=> SELECT substring(fld_1 from 1 for 2) as id_tag,fld_2, count(*)
from storage_test group by substring(fld_1 from 1 for 2),fld_2;
id_tag | fld_2 | count
--------+-------+-------
VK | four | 1
VK | one | 1
FB | one | 1
FB | three | 1
OK | two | 1
OK | three | 1
Thank you
Alex
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
