From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Counting different strings (OK%, FB%) in same table, grouped by week number |
Date: | 2012-02-22 20:56:08 |
Message-ID: | 81FCB66C-C678-488D-A3A0-233820E0540D@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Feb 22, 2012, at 15:36, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> 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(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
>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2012-02-22 21:10:16 | Re: Counting different strings (OK%, FB%) in same table, grouped by week number |
Previous Message | Alexander Farber | 2012-02-22 20:36:45 | Counting different strings (OK%, FB%) in same table, grouped by week number |