Date: Thu, 23 Jul 2009 08:57:22 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Need magical advice for counting NOTHING
Message-ID: <20090723065722.GA28356@a-kretschmer.de>
References: <4A67EEB2.1070302@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4A67EEB2.1070302@gmx.net>
User-Agent: Mutt/1.5.9i

In response to Andreas :
> 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 ?

without the WHERE-condition:


test=*# select * from log;
 log_id | log_type_fk | user_fk |         ts
--------+-------------+---------+---------------------
      1 |           1 |       1 | 2009-07-23 00:00:00
      2 |           2 |       2 | 2009-07-23 00:00:00
(2 rows)

test=*# select * from users;
 user_id | user_name
---------+-----------
       1 | user1
       2 | user2
(2 rows)

test=*# select * from log_type;
 log_type_id | log_type
-------------+----------
           1 | type1
           2 | type2
(2 rows)

test=*# select foo.user_name, foo.log_type, sum(case when log_type_fk is
not null then 1 else 0 end) from (select user_id, user_name,
log_type_id, log_type from users cross join log_type) foo full join log
on ((foo.user_id, foo.log_type_id)=(log.user_fk, log.log_type_fk)) group
by 1,2 order by 1,2;
 user_name | log_type | sum
-----------+----------+-----
 user1     | type1    |   1
 user1     | type2    |   0
 user2     | type1    |   0
 user2     | type2    |   1
(4 rows)


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net