On Thu, Jul 23, 2009 at 1:01 AM, Andreas<maps...@gmx.net> wrote: > 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
create table users (user_id integer, user_name varchar); create table log_type (log_type_id integer, log_type integer); create table log (log_id integer, log_type_fk integer, user_fk integer); insert into log_type (log_type_id, log_type) values (1, 1); insert into log_type (log_type_id, log_type) values (2, 2); insert into users (user_id, user_name) values (1, 'a'); insert into users (user_id, user_name) values (2, 'b'); insert into log (log_id, log_type_fk, user_fk) values (1, 1, 1); insert into log (log_id, log_type_fk, user_fk) values (2, 2, 1); insert into log (log_id, log_type_fk, user_fk) values (3, 2, 1); insert into log (log_id, log_type_fk, user_fk) values (4, 1, 2); SELECT user_name, log_type.log_type, sum((log_type_fk IS NOT NULL)::integer) AS count FROM users JOIN log_type ON (true) LEFT JOIN log ON (user_id = user_fk AND log.log_type_fk = log_type.log_type) GROUP BY user_name, log_type.log_type ORDER BY user_name, log_type.log_type; user_name | log_type | count -----------+----------+------- a | 1 | 1 a | 2 | 2 a | 3 | 0 b | 1 | 1 b | 2 | 0 b | 3 | 0 -- Glenn Maynard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
