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]

 



-----Original Message-----
From: Alexander Farber [mailto:alexander.farber@xxxxxxxxx] 
Sent: Wednesday, February 22, 2012 4:10 PM
To: David Johnston
Cc: pgsql-general
Subject: Re:  Counting different strings (OK%, FB%) in same table,
grouped by week number

Thank you David -

On Wed, Feb 22, 2012 at 9:56 PM, David Johnston <polobo@xxxxxxxxx> wrote:
> 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.
>

this works well, but I'm curious
how'd you count unknown users here?

pref=> SELECT yw,
SUM(CASE WHEN id ~ '^OK' THEN 1 ELSE 0 END) AS "Odnoklassniki", SUM(CASE
WHEN id ~ '^MR' THEN 1 ELSE 0 END) AS "Mail.ru", SUM(CASE WHEN id ~ '^VK'
THEN 1 ELSE 0 END) AS "Vkontakte", SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0
END) AS "Facebook", SUM(CASE WHEN id ~ '^GG' THEN 1 ELSE 0 END) AS "Google",
SUM(CASE WHEN id ~ '^DE' THEN 1 ELSE 0 END) AS "Preferans.de",
count(*) AS "Total"
from pref_money group by yw order by yw desc;

   yw    | Odnoklassniki | Mail.ru | Vkontakte | Facebook | Google |
Preferans.de | Total
---------+---------------+---------+-----------+----------+--------+--------
------+-------
 2012-08 |          2260 |     245 |       185 |       32 |      0 |
       314 |  3036
 2012-07 |          3074 |     338 |       267 |       32 |      0 |
       386 |  4097
 2012-06 |          3044 |     328 |       288 |       37 |      0 |
       393 |  4090
 2012-05 |          3092 |     347 |       268 |       46 |      2 |
       400 |  4155
 2012-04 |          3091 |     334 |       249 |       41 |      0 |
       402 |  4117


----------------------------------------------------------------------------
-------

Brute Force:

When id does not match the expression "starts with one of the following:
'OK', 'MR', etc..."

CASE WHEN id !~ '^(OK|MR|VK|FB|GG|DE)' THEN 1 ELSE 0 END AS "Undefined"

David J.





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