X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C1A7DDAF53 for ; Wed, 26 Oct 2005 15:27:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23418-08 for ; Wed, 26 Oct 2005 18:27:32 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 5BF11DAECC for ; Wed, 26 Oct 2005 15:27:31 -0300 (ADT) Received: (qmail 24970 invoked by uid 500); 26 Oct 2005 18:29:10 -0000 Date: Wed, 26 Oct 2005 13:29:10 -0500 From: Bruno Wolff III To: Volkan YAZICI Cc: pgsql-sql@postgresql.org Subject: Re: Combining two SELECTs by same filters Message-ID: <20051026182910.GH11447@wolff.to> Mail-Followup-To: Bruno Wolff III , Volkan YAZICI , pgsql-sql@postgresql.org References: <7104a7370510260816l9262d19j808d23c023e8445e@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <7104a7370510260816l9262d19j808d23c023e8445e@mail.gmail.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 required=5 tests=[AWL=0.007] X-Spam-Level: X-Archive-Number: 200510/342 X-Sequence-Number: 23195 On Wed, Oct 26, 2005 at 18:16:13 +0300, Volkan YAZICI wrote: > And I want to collect the count of sales at hour = 21 and hour = 22. > For this purpose, I'm using below SELECT query: > > => SELECT > -> (SELECT count(id) FROM sales > -> WHERE id = 2 > -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'), > -> (SELECT count(id) FROM sales > -> WHERE id = 2 > -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00'); > ?column? | ?column? > ----------+---------- > 6 | 2 > (1 row) > > Isn't it possible to combine these two SELECTs as one. Because one of > their filters are same: id = 2. I'm just trying to avoid making 2 > scans with nearly same filters. Use an OR clause when checking the time. You will need to enclose it in parenthesis because AND binds tighter than OR. For consecutive hours you could use a range test. (In fact you could use a range test even for one hour and it might be fasterdepending on your data and what indexes you have.)