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

Sent from Samsung mobile

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> 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(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...
>
>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(dot)klaver(at)gmail(dot)com
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
>