Date: Mon, 1 Feb 2010 10:03:28 +0100
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: combine SQL SELECT statements into one
Message-ID: <20100201090328.GC13395@a-kretschmer.de>
References: <995516.81823.qm@web113717.mail.gq1.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <995516.81823.qm@web113717.mail.gq1.yahoo.com>
User-Agent: Mutt/1.5.9i

In response to Neil Stlyz :
> Good Evening, Good Morning Wherever you are whenever you may be reading this.
> 
> I am new to this email group and have some good experience with SQL and
> PostgreSQL database.
> 
> 
> I am currently working on a PHP / PostgreSQL project and I came upon something
> I could not figure out in SQL. I was wondering if anyone here could take a look
> and perhaps offer some guidance or assistance in helping me write this SQL
> query.
> 
> Please Consider the following information:
> -------------------------------------------
> 
> I have a postgresql table called 'inventory' that includes two fields: 'model'
> which is a character varying field and 'modified' which is a timestamp field.
> 
> So the table inventory looks something like this:
> 
> 
>  model                          modified
> -------------                ----------
> I778288176                2010-02-01 08:27:00
> I778288176                 2010-01-31 11:23:00
> I778288176                 2010-01-29 10:46:00
> JKLM112345              2010-02-01 08:25:00
> JKLM112345              2010-01-31 09:52:00
> JKLM112345              2010-01-28 09:44:00
> X22TUNM765            2010-01-17 10:13:00
> V8893456T6               2010-01-01 09:17:00
> 
>  
> 
> Now with the table, fields and data in mind look at the following three
> queries:
> 
>  
> 
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01';
> 
>  
> 
> All three of the above queries work and provide results. However, I want to
> combine the three into one SQL Statement that hits the database one time. How
> can I do this in one SQL Statement? Is it possible with sub select?
> 
>  
> 
> Here is what result I am looking for from one SELECT statement using the data
> example from above:
> 
>  
> 
> count1 |  count2  | count3
> -------------------------------
>  2              2              4
> 
> 
> Can this be done with ONE SQL STATEMENT? touching the database only ONE time?

test=# select * from inventory ;
   model    |      modified
------------+---------------------
 I778288176 | 2010-02-01 08:27:00
 I778288176 | 2010-01-31 11:23:00
 I778288176 | 2010-01-29 10:46:00
 JKLM112345 | 2010-02-01 08:25:00
 JKLM112345 | 2010-01-31 09:52:00
 JKLM112345 | 2010-01-28 09:44:00
 X22TUNM765 | 2010-01-17 10:13:00
 V8893456T6 | 2010-01-01 09:17:00
(8 rows)

test=*# select count(distinct count1), count(distinct count2),
count(distinct count3) from (select distinct case when modified >=
'2010-02-01' then model else null end as count1, case when modified >=
'2010-01-20' then model else null end as count2, case when modified >=
'2010-01-01' then model else null end as count3 from inventory) foo ;
 count | count | count
-------+-------+-------
     2 |     2 |     4
(1 row)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99