Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6350C634636 for ; Thu, 23 Jul 2009 04:18:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 36543-02-7 for ; Thu, 23 Jul 2009 04:17:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.8]) by mail.postgresql.org (Postfix) with ESMTP id 1F805634B68 for ; Thu, 23 Jul 2009 04:10:11 -0300 (ADT) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0MKt2u-1MTsRM3FGl-000BHg; Thu, 23 Jul 2009 09:10:09 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1MTsRM-0001d1-00 for pgsql-sql@postgresql.org; Thu, 23 Jul 2009 09:10:08 +0200 Date: Thu, 23 Jul 2009 09:10:08 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Need magical advice for counting NOTHING Message-ID: <20090723071007.GB28356@a-kretschmer.de> References: <4A67EEB2.1070302@gmx.net> <20090723065722.GA28356@a-kretschmer.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20090723065722.GA28356@a-kretschmer.de> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX18YTuwtX2JjxNG+exlIIL1y8XyqRzs1p1jCn7n +6+yj2P1o9UDmcXoKplKhXag3XPqjKeZ2irVi9G5yYQGBfxqha BcOyMjvodYFouABP0x5yg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.026 tagged_above=-10 required=5 tests=AWL=0.026 X-Spam-Level: X-Archive-Number: 200907/112 X-Sequence-Number: 32853 In response to A. Kretschmer : > 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) Update: select foo.user_name, foo.log_type, count(log_type_fk) ... -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net