Sent from Samsung mobile Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: >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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
