Message-ID: <4A67EEB2.1070302@gmx.net>
Date: Thu, 23 Jul 2009 07:01:38 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Need magical advice for counting NOTHING
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
The source select counts log-events per user.
All is well when a user has at least one event per log_type in the log 
within a given timespan.
If one log_type is missing COUNT() has nothing to count and there is 
expectedly no result line that says 0.
BUT I need this 0-line because of a crosstab.  :(
I need to know how to prevent in my crosstab categories on the right to 
slip to the left, when the left category is emptyy.

Server 8.3.5

3 tables
log  (log_id, log_type_fk, user_fk, ts timestamp, ...)
users  (user_id, user_name, ...)
log_type  (log_type_id, log_type)
There are 3 events as log_type.

I naively tried
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

This results e.g. to

user1, type1, 2
user1, type2, 3
user1, type3, 7
user2, type1, 11
user2, type3, 17

but I needed also
user2, type2, 0

How would I get there ?

Regards
Andreas