Message-ID: <4A680750.1020006@Sheeky.Biz>
Date: Thu, 23 Jul 2009 16:16:40 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.22 (Macintosh/20090605)
MIME-Version: 1.0
To: Andreas <maps.on@gmx.net>
CC: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Need magical advice for counting NOTHING
References: <4A67EEB2.1070302@gmx.net>
In-Reply-To: <4A67EEB2.1070302@gmx.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andreas wrote:
> 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
> 
SELECT user_name, log_type_fk, COUNT(log_type_fk)

FROM log
RIGHT JOIN users ON (user_id = user_fk)

WHERE ts  BETWEEN  sometime  AND another
OR ts IS null

GROUP BY user_name, log_type_fk
ORDER BY user_name, log_type_fk


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz