Thread: combine SQL SELECT statements into one
Good Evening, Good Morning Wherever you are whenever you may be reading this.
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:
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';
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
Please let me know.
Thanx> :)
|
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
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 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
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 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
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 >