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

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@gmail.com