Re: Need magical advice for counting NOTHING

Hello,

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