From: | Glenn Maynard <glenn(at)zewt(dot)org> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Need magical advice for counting NOTHING |
Date: | 2009-07-23 08:23:29 |
Message-ID: | bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Jul 23, 2009 at 1:01 AM, Andreas<maps(dot)on(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | bartjoosen | 2009-07-23 09:59:05 | using count in other column |
Previous Message | Richard Huxton | 2009-07-23 08:06:50 | Re: Bit by "commands ignored until end of transaction block" again |