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

-----Original Message-----
From: Alexander Farber [mailto:alexander(dot)farber(at)gmail(dot)com]
Sent: Wednesday, February 22, 2012 4:10 PM
To: David Johnston
Cc: pgsql-general
Subject: Re: [GENERAL] 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

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

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.