pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Glenn Maynard <glenn@zewt.org>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Need magical advice for counting NOTHING
Date: Thu, 23 Jul 2009 04:23:29 -0400
Message-ID: <bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com> (raw)
In-Reply-To: <4A67EEB2.1070302@gmx.net>
References: <4A67EEB2.1070302@gmx.net>

On Thu, Jul 23, 2009 at 1:01 AM, Andreas<maps.on@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



view thread (7+ messages)  latest in thread

Message-ID: <bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>
Permalink:  ../bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com/
Also on:    postgresql.org/message-id/bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: glenn@zewt.org
  Subject: Re: Need magical advice for counting NOTHING
  In-Reply-To: <bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox