[GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
On Feb 22, 2012, at 15:36, 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... > > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
-Original Message- From: Alexander Farber [mailto:alexander.far...@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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
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: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
Sent from Samsung mobile Adrian Klaver wrote: >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.kla...@gmail.com > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
