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]

 



On Feb 22, 2012, at 15:36, Alexander Farber <alexander.farber@xxxxxxxxx> 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...
> 
> 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.



-- 
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