Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux