Message-ID: <4B66B83F.3000708@beecom.ch>
Date: Mon, 01 Feb 2010 12:17:19 +0100
From: Leo Mannhart <leo.mannhart@beecom.ch>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: msi77 <msi77@yandex.ru>
CC: Neil Stlyz <neilstylz@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: combine SQL SELECT statements into one
References: <995516.81823.qm@web113717.mail.gq1.yahoo.com>
	<6951265019068@webmail103.yandex.ru>
In-Reply-To: <6951265019068@webmail103.yandex.ru>
Content-Type: text/plain; charset=KOI8-R
Content-Transfer-Encoding: quoted-printable

msi77 wrote:
> Hi,
>=20
> SELECT
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D '2010-=
02-01') as count1,
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D '2010-=
01-20') as count2,
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >=3D '2010-=
01-01') as count3

But this statement will seq scan the table inventory three times as an
explain analyze easily will show, while the solution from Andreas will
do only one seq scan. This can be a big difference, depending on the
size of the table.


>=20
> Serge
>=20
>> Good Evening, Good Morning Wherever you are whenever you may be readin=
g this.=20
>> I am new to this email group and have some good experience with SQL an=
d PostgreSQL database.=20
>> I am currently working on a PHP / PostgreSQL project and I came upon s=
omething I could not figure out in SQL. I was wondering if anyone here co=
uld take a look and perhaps offer some guidance or assistance in helping =
me write this SQL query.=20
>> Please Consider the following information:=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 tim=
estamp field.=20
>> So the table inventory looks something like this:=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
>> Now with the table, fields and data in mind look at the following thre=
e queries:=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
>> All three of the above queries work and provide results. However, I wa=
nt 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 se=
lect?=20
>> Here is what result I am looking for from one SELECT statement using t=
he data example from above:=20
>> count1 |  count2  | count3
>> -------------------------------
>> 2              2              4=20
>> Can this be done with ONE SQL STATEMENT? touching the database only ON=
E time?=20
>> Please let me know.=20
>> Thanx> :)
>> NEiL=20
>>
>=20
> =FA=C4=C5=D3=D8 =D3=D0=C1=CD=C1 =CE=C5=D4 http://mail.yandex.ru/nospam/=
sign
>=20