I'd code it more general to allow for any user type: select yw, substr(id,1,2) as user_type, count(1) from pref_money group by yw, user_type You can use some clever pivoting to get the user_types to be columns, but I see no need to waste db cycles. You can get the report you want by one-pass processing of the above result set. If you have mountains of data I'd precompute, before insert or during insert by a trigger, the user_type and store it separately. Kiriakos http://www.mockbites.com On Feb 22, 2012, at 3: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... > > Thank you > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
