Message-ID: <4A6A513E.2090507@free.fr>
Date: Sat, 25 Jul 2009 02:26:38 +0200
From: nha <lyondif02@free.fr>
Organization: Lyon, Rhone, France
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: Need magical advice for counting NOTHING
References: <4A67EEB2.1070302@gmx.net>
	<bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>
In-Reply-To: <bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Hello,

Le 23/07/09 10:23, Glenn Maynard a écrit :
> 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
> [...] 
> 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;
> [...]

In the same direction as Andreas Krestchmer and Glenn Maynard (thanks to
the latter for DDL assumed statements), the following query should also
suit:

SELECT user_name, log_type, COUNT(log_type_fk)
FROM (users CROSS JOIN log_type)
LEFT JOIN log ON (user_id = user_fk AND log_type_id = log_type_fk)
WHERE (ts IS BETWEEN sometime AND another)
GROUP BY user_name, log_type
ORDER BY user_name, log_type

It is syntactically nearer the original query and includes no class
operator. Here are the two main ideas:
- Building all the possible couples of user name and log type by
cross-joining users and log_type tables;
- Counting rows in log table matching each couple (user, log_type) from
the previous cross-join (LEFT JOIN ensures that each row of the table on
the left is mined).

While it is formally assumed that user_id and log_type_id are
respectively keys for users and log_type tables, it is semantically
admitted here that user_name identifies user_id in users table and
log_type identifies log_type_id in log_type table.

Regards.
--
nha / Lyon / France.