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