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> :)
|
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
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.
******************************************************************************
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/
> -----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
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.
> > -----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