From: | Shane Ambler <pgsql(at)Sheeky(dot)Biz> |
---|---|
To: | Andreas <maps(dot)on(at)gmx(dot)net> |
Cc: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Need magical advice for counting NOTHING |
Date: | 2009-07-23 06:46:40 |
Message-ID: | 4A680750.1020006@Sheeky.Biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Tolley | 2009-07-23 06:46:56 | Re: Bit by "commands ignored until end of transaction block" again |
Previous Message | Richard Huxton | 2009-07-23 06:41:07 | Re: Bit by "commands ignored until end of transaction block" again |