Re: combine SQL SELECT statements into one - Mailing list pgsql-general

From Florent THOMAS
Subject Re: combine SQL SELECT statements into one
Date
Msg-id 1265013324.23250.21.camel@Dell_Inspiron
Whole thread Raw
In response to combine SQL SELECT statements into one  (Neil Stlyz <neilstylz@yahoo.com>)
List pgsql-general
Hi,

If I were you, I worked like this.
First make a union of those three query
Then make a crosstab : http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html
documented here : http://www.postgresql.org/docs/8.4/interactive/tablefunc.html


Le dimanche 31 janvier 2010 à 23:36 -0800, Neil Stlyz a écrit :
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?

Please let me know.

 

Thanx> :)
NEiL

 



pgsql-general by date:

Previous
From: Neil Stlyz
Date:
Subject: combine SQL SELECT statements into one
Next
From: Chris.Ellis@shropshire.gov.uk
Date:
Subject: Re: combine SQL SELECT statements into one