Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 93E4C63249E for ; Mon, 1 Feb 2010 04:31:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29036-07 for ; Mon, 1 Feb 2010 08:31:39 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by mail.postgresql.org (Postfix) with ESMTP id BD46F6361B2 for ; Mon, 1 Feb 2010 04:31:39 -0400 (AST) Received: from [192.168.0.11] (ara66-1-82-242-236-166.fbx.proxad.net [82.242.236.166]) by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis) id 0MhgyF-1NORXz47YR-00MzsU; Mon, 01 Feb 2010 09:31:34 +0100 Subject: Re: combine SQL SELECT statements into one From: Florent THOMAS To: postgesql general Cc: Neil Stlyz In-Reply-To: <995516.81823.qm@web113717.mail.gq1.yahoo.com> References: <995516.81823.qm@web113717.mail.gq1.yahoo.com> Content-Type: multipart/alternative; boundary="=-2ybhxCmzwKjm5qiQf7u0" Date: Mon, 01 Feb 2010 09:35:24 +0100 Message-Id: <1265013324.23250.21.camel@Dell_Inspiron> Mime-Version: 1.0 X-Mailer: Evolution 2.24.5-1.3mdv2009.0 X-Provags-ID: V01U2FsdGVkX1+w75zHvkYLQjwpKxN7xIngkaVVMh4RiyU71e2 RAGQWPWyYQyFf9sTP5sKZ06pTgQmN8zFHgKttMqPcoxK3a/1wm 3bPo4Wf91auJHxhCl37UIfSQQmnFbql X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/6 X-Sequence-Number: 158782 --=-2ybhxCmzwKjm5qiQf7u0 Content-Type: text/plain; charset="ISO-8859-15" Content-Transfer-Encoding: quoted-printable Hi, If I were you, I worked like this. First make a union of those three query=20 Then make a crosstab : http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queri= es-in-PostgreSQL-using-tablefunc-contrib.html documented here : http://www.postgresql.org/docs/8.4/interactive/tablefunc.html Le dimanche 31 janvier 2010 =E0 23:36 -0800, Neil Stlyz a =E9crit : > Good Evening, Good Morning Wherever you are whenever you may be > reading this.=20 >=20 > I am new to this email group and have some good experience with SQL > and PostgreSQL database.=20 >=20 >=20 > 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.=20 >=20 > Please Consider the following information:=20 > -------------------------------------------=20 >=20 > 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.=20 >=20 > So the table inventory looks something like this:=20 >=20 >=20 > model modified > ------------- ---------- > I778288176 2010-02-01 08:27:00=20 > 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=20 >=20 > =20 >=20 > Now with the table, fields and data in mind look at the following > three queries:=20 >=20 > =20 >=20 > SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D > '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D > '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D > '2010-01-01';=20 >=20 > =20 >=20 > 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?=20 >=20 > =20 >=20 > Here is what result I am looking for from one SELECT statement using > the data example from above:=20 >=20 > =20 >=20 > count1 | count2 | count3 > ------------------------------- > 2 2 4=20 >=20 >=20 > Can this be done with ONE SQL STATEMENT? touching the database only > ONE time?=20 >=20 > Please let me know.=20 >=20 > =20 >=20 > Thanx> :) > NEiL=20 >=20 > =20 >=20 >=20 >=20 --=-2ybhxCmzwKjm5qiQf7u0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 7bit 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

 

--=-2ybhxCmzwKjm5qiQf7u0--