Need magical advice for counting NOTHING
| От | Andreas |
|---|---|
| Тема | Need magical advice for counting NOTHING |
| Дата | |
| Msg-id | 4A67EEB2.1070302@gmx.net обсуждение исходный текст |
| Ответы |
Re: Need magical advice for counting NOTHING
Re: Need magical advice for counting NOTHING Re: Need magical advice for counting NOTHING |
| Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: