pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcombine SQL SELECT statements into one
11+ messages / 9 participants
[nested] [flat]
* combine SQL SELECT statements into one
@ 2010-02-01 07:36 Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 08:35 ` Re: combine SQL SELECT statements into one Florent THOMAS <mailinglist@tdeo.fr>
2010-02-01 09:03 ` Re: combine SQL SELECT statements into one A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-02-01 09:08 ` Re: combine SQL SELECT statements into one Chris.Ellis@shropshire.gov.uk
2010-02-01 10:10 ` Re: combine SQL SELECT statements into one msi77 <msi77@yandex.ru>
2010-02-01 10:11 ` Re: combine SQL SELECT statements into one msi77 <msi77@yandex.ru>
2010-02-01 12:08 ` Re: combine SQL SELECT statements into one Sam Mason <sam@samason.me.uk>
0 siblings, 6 replies; 11+ messages in thread
From: Neil Stlyz @ 2010-02-01 07:36 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 08:35 ` Florent THOMAS <mailinglist@tdeo.fr>
5 siblings, 0 replies; 11+ messages in thread
From: Florent THOMAS @ 2010-02-01 08:35 UTC (permalink / raw)
To: postgesql general <pgsql-general@postgresql.org>; +Cc: Neil Stlyz <neilstylz@yahoo.com>
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-ta...
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
>
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 09:03 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
5 siblings, 0 replies; 11+ messages in thread
From: A. Kretschmer @ 2010-02-01 09:03 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 09:08 ` Chris.Ellis@shropshire.gov.uk
2010-02-01 19:09 ` Re: combine SQL SELECT statements into one Igor Neyman <ineyman@perceptron.com>
5 siblings, 1 reply; 11+ messages in thread
From: Chris.Ellis@shropshire.gov.uk @ 2010-02-01 09:08 UTC (permalink / raw)
To: neilstylz@yahoo.com; +Cc: pgsql-general@postgresql.org
Hi
pgsql-general-owner@postgresql.org wrote on 02/01/2010 07:36:55 AM:
> Good Evening, Good Morning Wherever you are whenever you may be reading
this.
>
>
snip
>
> count1 | count2 | count3
> -------------------------------
> 2 2 4
>
> Can this be done with ONE SQL STATEMENT? touching the database only ONE
time?
You can do the following:
SELECT
(SELECT COUNT(distinct model) FROM inventory WHERE modified >=
'2010-02-01') AS "COUNT_1",
(SELECT COUNT(distinct model) FROM inventory WHERE modified >=
'2010-01-20') AS "COUNT_2",
(SELECT COUNT(distinct model) FROM inventory WHERE modified >=
'2010-01-01') AS "COUNT_3"
;
PostgreSQL allows sub-queries in the select list as long as the sub-query
returns one column
Job done
> Please let me know.
>
> Thanx> :)
> NEiL
>
Chris Ellis
******************************************************************************
If you are not the intended recipient of this email please do not send it on
to others, open any attachments or file the email locally.
Please inform the sender of the error and then delete the original email.
For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf
******************************************************************************
Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT.
******************************************************************************
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 09:08 ` Re: combine SQL SELECT statements into one Chris.Ellis@shropshire.gov.uk
@ 2010-02-01 19:09 ` Igor Neyman <ineyman@perceptron.com>
2010-02-01 20:25 ` Re: combine SQL SELECT statements into one Scott Marlowe <scott.marlowe@gmail.com>
2010-02-01 20:39 ` Re: combine SQL SELECT statements into one Chris.Ellis@shropshire.gov.uk
0 siblings, 2 replies; 11+ messages in thread
From: Igor Neyman @ 2010-02-01 19:09 UTC (permalink / raw)
To: Chris.Ellis@shropshire.gov.uk; neilstylz@yahoo.com; +Cc: pgsql-general@postgresql.org
> -----Original Message-----
> From: Chris.Ellis@shropshire.gov.uk
> [mailto:Chris.Ellis@shropshire.gov.uk]
> Sent: Monday, February 01, 2010 4:08 AM
> To: neilstylz@yahoo.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: combine SQL SELECT statements into one
>
>
> Hi
>
> pgsql-general-owner@postgresql.org wrote on 02/01/2010 07:36:55 AM:
>
> > Good Evening, Good Morning Wherever you are whenever you
> may be reading this.
> >
> >
> snip
> >
> > count1 | count2 | count3
> > -------------------------------
> > 2 2 4
> >
> > Can this be done with ONE SQL STATEMENT? touching the
> database only ONE time?
>
> You can do the following:
>
> SELECT
> (SELECT COUNT(distinct model) FROM inventory WHERE
> modified >= '2010-02-01') AS "COUNT_1",
> (SELECT COUNT(distinct model) FROM inventory WHERE
> modified >= '2010-01-20') AS "COUNT_2",
> (SELECT COUNT(distinct model) FROM inventory WHERE
> modified >= '2010-01-01') AS "COUNT_3"
> ;
>
> PostgreSQL allows sub-queries in the select list as long as
> the sub-query returns one column
>
> Job done
>
> > Please let me know.
> >
> > Thanx> :)
> > NEiL
> >
>
> Chris Ellis
>
> **************************************************************
> ****************
>
> If you are not the intended recipient of this email please do
> not send it on
>
> to others, open any attachments or file the email locally.
>
> Please inform the sender of the error and then delete the
> original email.
>
> For more information, please refer to
> http://www.shropshire.gov.uk/privacy.nsf
>
> **************************************************************
> ****************
>
> Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT.
>
> **************************************************************
> ****************
>
Original poster asked for the sql that will touch inventory table only
once.
Your statement (with 3 subqueries) will do it 3 times.
Igor Neyman
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 09:08 ` Re: combine SQL SELECT statements into one Chris.Ellis@shropshire.gov.uk
2010-02-01 19:09 ` Re: combine SQL SELECT statements into one Igor Neyman <ineyman@perceptron.com>
@ 2010-02-01 20:25 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2010-02-01 20:25 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Chris.Ellis@shropshire.gov.uk; neilstylz@yahoo.com; pgsql-general@postgresql.org
On Mon, Feb 1, 2010 at 12:09 PM, Igor Neyman <ineyman@perceptron.com> wrote:
> Original poster asked for the sql that will touch inventory table only
> once.
>
> Your statement (with 3 subqueries) will do it 3 times.
I'm pretty sure that starting with 8.3 the engine will collapse all
those into one seq scan internally.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 09:08 ` Re: combine SQL SELECT statements into one Chris.Ellis@shropshire.gov.uk
2010-02-01 19:09 ` Re: combine SQL SELECT statements into one Igor Neyman <ineyman@perceptron.com>
@ 2010-02-01 20:39 ` Chris.Ellis@shropshire.gov.uk
1 sibling, 0 replies; 11+ messages in thread
From: Chris.Ellis@shropshire.gov.uk @ 2010-02-01 20:39 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; neilstylz@yahoo.com, <pgsql-general@postgresql.org>
> > -----Original Message-----
> > From: Chris.Ellis@shropshire.gov.uk
> > [mailto:Chris.Ellis@shropshire.gov.uk]
> > Sent: Monday, February 01, 2010 4:08 AM
> > To: neilstylz@yahoo.com
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: combine SQL SELECT statements into one
> >
> >
> > Hi
> >
> > pgsql-general-owner@postgresql.org wrote on 02/01/2010 07:36:55 AM:
> >
> > > Good Evening, Good Morning Wherever you are whenever you
> > may be reading this.
> > >
> > >
> > snip
> > >
> > > count1 | count2 | count3
> > > -------------------------------
> > > 2 2 4
> > >
> > > Can this be done with ONE SQL STATEMENT? touching the
> > database only ONE time?
> >
> > You can do the following:
> >
> > SELECT
> > (SELECT COUNT(distinct model) FROM inventory WHERE
> > modified >= '2010-02-01') AS "COUNT_1",
> > (SELECT COUNT(distinct model) FROM inventory WHERE
> > modified >= '2010-01-20') AS "COUNT_2",
> > (SELECT COUNT(distinct model) FROM inventory WHERE
> > modified >= '2010-01-01') AS "COUNT_3"
> > ;
> >
> > PostgreSQL allows sub-queries in the select list as long as
> > the sub-query returns one column
> >
> > Job done
> >
> > > Please let me know.
> > >
> > > Thanx> :)
> > > NEiL
> > >
> >
> > Chris Ellis
> >
> > **************************************************************
> > ****************
> >
> > If you are not the intended recipient of this email please do
> > not send it on
> >
> > to others, open any attachments or file the email locally.
> >
> > Please inform the sender of the error and then delete the
> > original email.
> >
> > For more information, please refer to
> > http://www.shropshire.gov.uk/privacy.nsf
> >
> > **************************************************************
> > ****************
> >
> > Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT.
> >
> > **************************************************************
> > ****************
> >
>
> Original poster asked for the sql that will touch inventory table only
> once.
>
> Your statement (with 3 subqueries) will do it 3 times.
> Igor Neyman
>
---
I think you will find that the poster asked to touch the DATABASE not the
TABLE only once:
'Can this be done with ONE SQL STATEMENT? touching the database
only ONE time?'
While the sugested query might not me as optimised as possible, it
demonstrates a possible method of folding multiple select statements into
one select statement. This seemed
main purpose of this post. I made the assumption that the intent was to
reduce the overhead and latency caused from sending multiple statements.
Chris Ellis
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 10:10 ` msi77 <msi77@yandex.ru>
5 siblings, 0 replies; 11+ messages in thread
From: msi77 @ 2010-02-01 10:10 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
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 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
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 10:11 ` msi77 <msi77@yandex.ru>
2010-02-01 11:17 ` Re: combine SQL SELECT statements into one Leo Mannhart <leo.mannhart@beecom.ch>
5 siblings, 1 reply; 11+ messages in thread
From: msi77 @ 2010-02-01 10:11 UTC (permalink / raw)
To: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
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 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
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 10:11 ` Re: combine SQL SELECT statements into one msi77 <msi77@yandex.ru>
@ 2010-02-01 11:17 ` Leo Mannhart <leo.mannhart@beecom.ch>
0 siblings, 0 replies; 11+ messages in thread
From: Leo Mannhart @ 2010-02-01 11:17 UTC (permalink / raw)
To: msi77 <msi77@yandex.ru>; +Cc: Neil Stlyz <neilstylz@yahoo.com>; pgsql-sql
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 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
>>
>
> Здесь спама нет http://mail.yandex.ru/nospam/sign
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: combine SQL SELECT statements into one
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
@ 2010-02-01 12:08 ` Sam Mason <sam@samason.me.uk>
5 siblings, 0 replies; 11+ messages in thread
From: Sam Mason @ 2010-02-01 12:08 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Sun, Jan 31, 2010 at 11:36:55PM -0800, Neil Stlyz wrote:
> 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?
If you only wanted a single table scan, you could use CASE:
SELECT
COUNT(DISTINCT CASE WHEN modified >= '2010-02-01' THEN model END) AS c1,
COUNT(DISTINCT CASE WHEN modified >= '2010-01-20' THEN model END) AS c2,
COUNT(DISTINCT CASE WHEN modified >= '2010-01-01' THEN model END) AS c3
FROM inventory
WHERE modified >= '2010-01-01';
Note that the final WHERE clause isn't really needed, it'll just make
things a bit faster and give PG the opportunity to use an INDEX if it
looks helpful. If you're generating the above from code, you may want
to use the LEAST function in SQL rather than working out the smallest
value in your code, i.e:
WHERE modified >= LEAST('2010-02-01','2010-02-01','2010-02-01');
--
Sam http://samason.me.uk/
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-02-01 20:39 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-01 07:36 combine SQL SELECT statements into one Neil Stlyz <neilstylz@yahoo.com>
2010-02-01 08:35 ` Florent THOMAS <mailinglist@tdeo.fr>
2010-02-01 09:03 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-02-01 09:08 ` Chris.Ellis@shropshire.gov.uk
2010-02-01 19:09 ` Igor Neyman <ineyman@perceptron.com>
2010-02-01 20:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-02-01 20:39 ` Chris.Ellis@shropshire.gov.uk
2010-02-01 10:10 ` msi77 <msi77@yandex.ru>
2010-02-01 10:11 ` msi77 <msi77@yandex.ru>
2010-02-01 11:17 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-02-01 12:08 ` Sam Mason <sam@samason.me.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox