Re: [NOVICE] combine SQL SELECT statements into one - Mailing list pgsql-sql
From | A. Kretschmer |
---|---|
Subject | Re: [NOVICE] combine SQL SELECT statements into one |
Date | |
Msg-id | 20100201090328.GC13395@a-kretschmer.de Whole thread Raw |
In response to | combine SQL SELECT statements into one (Neil Stlyz <neilstylz@yahoo.com>) |
List | 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