-----Original Message----- From: Alexander Farber [mailto:alexander.farber@xxxxxxxxx] Sent: Wednesday, February 22, 2012 4:10 PM To: David Johnston Cc: pgsql-general Subject: 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
