In response to Andreas : > Hi, > The source select counts log-events per user. > All is well when a user has at least one event per log_type in the log > within a given timespan. > If one log_type is missing COUNT() has nothing to count and there is > expectedly no result line that says 0. > BUT I need this 0-line because of a crosstab. :( > I need to know how to prevent in my crosstab categories on the right to > slip to the left, when the left category is emptyy. > > Server 8.3.5 > > 3 tables > log (log_id, log_type_fk, user_fk, ts timestamp, ...) > users (user_id, user_name, ...) > log_type (log_type_id, log_type) > There are 3 events as log_type. > > I naively tried > SELECT user_name, log_type_fk, COUNT(log_type_fk) > FROM log > JOIN users ON (user_id = user_fk) > WHERE (ts IS BETWEEN sometime AND another) > GROUP BY user_name, log_type_fk > ORDER BY user_name, log_type_fk > > This results e.g. to > > user1, type1, 2 > user1, type2, 3 > user1, type3, 7 > user2, type1, 11 > user2, type3, 17 > > but I needed also > user2, type2, 0 > > How would I get there ?
without the WHERE-condition: test=*# select * from log; log_id | log_type_fk | user_fk | ts --------+-------------+---------+--------------------- 1 | 1 | 1 | 2009-07-23 00:00:00 2 | 2 | 2 | 2009-07-23 00:00:00 (2 rows) test=*# select * from users; user_id | user_name ---------+----------- 1 | user1 2 | user2 (2 rows) test=*# select * from log_type; log_type_id | log_type -------------+---------- 1 | type1 2 | type2 (2 rows) test=*# select foo.user_name, foo.log_type, sum(case when log_type_fk is not null then 1 else 0 end) from (select user_id, user_name, log_type_id, log_type from users cross join log_type) foo full join log on ((foo.user_id, foo.log_type_id)=(log.user_fk, log.log_type_fk)) group by 1,2 order by 1,2; user_name | log_type | sum -----------+----------+----- user1 | type1 | 1 user1 | type2 | 0 user2 | type1 | 0 user2 | type2 | 1 (4 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql