msi77 wrote:
> Hi,
>
> SELECT
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1,
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2,
> (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '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.
>
> Serge
>
>> 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
wonderingif 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
thathits 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
>>
>
> Здесь спама нет http://mail.yandex.ru/nospam/sign
>
