Counting different strings (OK%, FB%) in same table, grouped by week number

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Counting different strings (OK%, FB%) in same table, grouped by week number
Date: 2012-02-22 20:36:45
Message-ID: CAADeyWjXHQBPyww9JT0xEyKHNp=U82Ctxm0F9GWh7Awd2ppE7w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-02-22 20:56:08 Re: Counting different strings (OK%, FB%) in same table, grouped by week number
Previous Message Greg Williamson 2012-02-22 19:58:06 Re: Measuring replication lag time