Counting different strings (OK%, FB%) in same table, grouped by week number - Mailing list pgsql-general

From Alexander Farber
Subject Counting different strings (OK%, FB%) in same table, grouped by week number
Date
Msg-id CAADeyWjXHQBPyww9JT0xEyKHNp=U82Ctxm0F9GWh7Awd2ppE7w@mail.gmail.com
Whole thread Raw
Responses Re: Counting different strings (OK%, FB%) in same table, grouped by week number
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
List 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@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

pgsql-general by date:

Previous
From: Greg Williamson
Date:
Subject: Re: Measuring replication lag time
Next
From: David Johnston
Date:
Subject: Re: Counting different strings (OK%, FB%) in same table, grouped by week number