On Feb 22, 2012, at 15:36, Alexander Farber <alexander.farber@xxxxxxxxx> 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 > Straight SQL: SELECT SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS fb_cnt, repeat for each known type (and I generally code one for unknown as well). Depending of your use case building out the non-column version and pushing it into a PivotTable would work. There is also a crosstab module that you can use as well - though I have not used it myself. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
