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

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

Thank you David -

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

On 02/22/2012 12:36 PM, Alexander Farber 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...

How about?:

test=> \d storage_test
Table "public.storage_test"
Column | Type | Modifiers
---------+-----------------------+-----------
fld_1 | character varying |
fld_2 | character varying(10) |
fld_3 | character(5) |
fld_int | integer

test=> SELECT * from storage_test ;
fld_1 | fld_2 | fld_3 | fld_int
-------+-------+-------+---------
FB001 | one | | 4
FB002 | three | | 10
OK001 | three | | 5
OK002 | two | | 6
VK001 | one | | 9
VK002 | four | | 2

test=> SELECT substring(fld_1 from 1 for 2) as id_tag,fld_2, count(*)
from storage_test group by substring(fld_1 from 1 for 2),fld_2;
id_tag | fld_2 | count
--------+-------+-------
VK | four | 1
VK | one | 1
FB | one | 1
FB | three | 1
OK | two | 1
OK | three | 1

Thank you
Alex

--
Adrian Klaver
adrian.klaver@gmail.com

-----Original Message-----
From: Alexander Farber [mailto:alexander.farber@gmail.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@yahoo.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.

Sent from Samsung mobile

Adrian Klaver <adrian.klaver@gmail.com> wrote:

I'd code it more general to allow for any user type:

select
yw, substr(id,1,2) as user_type, count(1)
from
pref_money
group by
yw, user_type

You can use some clever pivoting to get the user_types to be columns, but I see no need to waste db cycles.
You can get the report you want by one-pass processing of the above result set.

If you have mountains of data I'd precompute, before insert or during insert by a trigger, the user_type and store it separately.

Kiriakos
http://www.mockbites.com

On Feb 22, 2012, at 3:36 PM, Alexander Farber wrote: