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(at)yahoo(dot)com> 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