combine SQL SELECT statements into one

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-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 :

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

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.
******************************************************************************

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

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

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.

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