From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Counting different strings (OK%, FB%) in same table, grouped by week number |
Date: | 2012-02-22 21:10:16 |
Message-ID: | CAADeyWg2vQJDGwuPxYU=0PUT+5fg5owvHVz934OucJ7dJbHm9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2012-02-22 21:14:42 | Re: Counting different strings (OK%, FB%) in same table, grouped by week number |
Previous Message | David Johnston | 2012-02-22 20:56:08 | Re: Counting different strings (OK%, FB%) in same table, grouped by week number |