Thread: Help with subselect (first time)
I will guess in advance that I'm missing something obvious, but I *think* I need a subselect here and I've never done those. So if anyone has a comment that will point me in the right direction, I'd appreciate it. The short version is that I am trying to create a summary table by month, and for all but one criterion I've done it. This is my first select query: select to_char(date, 'YYYY-MM') as month, sum(hrs_dual) as hrs_dual, sum(hrs_pic) as hrs_pic, sum(hrs_night) as hrs_night, sum(hrs_ifrsim) as hrs_ifrsim, sum(hrs_ifract) as hrs_ifract, sum(apps_ifrsim) as apps_ifrsim, sum(apps_ifract) as apps_ifract, sum(hrs_total) as hrs_total from flights group by month order by month asc; If that's a pain to read, it could be simplified to: select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as hrs_total from flights group by month order by month asc; This query allows me to get my total monthly flights as pilot-in- command, at night, and by other criteria which are stored as columns in the flights table. However, I have one criterion which depends on another table, and that is multi-engine time. This can be found via the following select query: simpatic_logbook=> select to_char(date, 'YYYY-MM') as month, sum(hrs_total) from flights,aircraft where flights.callsign=aircraft.callsign and aircraft.engines=2 group by month order by month asc; I then have two questions: 1. How do I (or even *can* I) integrate the second query into the first as a subselect? Each individual flight can only be made in one aircraft (I have to land to get off!), so hrs_total is all I need from flights, and I just have to check whether that aircraft's callsign is listed in the aircraft table as having engines=2. 2. Not all months have multi-engine time... will these months show up in the subselect with a zero or give an error? 3. I still find the subselect syntax confusing; I will continue to read the docs to get my head around it, though. Thanks for any pointers... I'll keep playing on this end. Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
Hi Group: I do apologize ahead of time. I have been reading through many gentoo posts addressing initdb problems with postgresql, but I cannot seem to figure out the problem on my own following along with the documentation and forum posts. I'm going around in circles uninstalling/re-installing. I removed Postgresql 7.4.6 and installed Postgresql ver. 8.0.1 I am using Gentoo Linux. 1. /etc/conf.d/postgresql says -- PGDATA=/var/lib/postgresql/data PGLOG=/var/lib/postgresql/data/postgresql.log PGUSER=postgres 2. I checked to make sure that /var/lib/postgresql/data is owned by postgres, and it is --- Under /var/lib, ls -al shows: drwxr-xr-x 3 postgres postgres 136 Jan 30 23:14 postgresql 3. I su postgres from root and set the PGDATA variable, but it all goes wrong from there. Here's the feedback I get when I try to complete the post-installation configuration: bash-2.05b# su postgres bash-2.05b$ export PGDATA=/var/lib/postgresql/data bash-2.05b$ echo $PGDATA /var/lib/postgresql/data bash-2.05b$ initdb could not change directory to "/home/floog" The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. initdb: directory "/var/lib/postgresql/data" exists but is not empty If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data". bash-2.05b$ whoami postgres bash-2.05b$ I cannot figure out why Postgresql wants to change to the /home/floog directory, and I do not understand why Postgresql needs an empty directory. I have tried in the past to create an empty directory in /usr/local/postgres , but then when I do initdb there, Postgres automatically creates the same files that were in /var/lib/postgresql/data , and then simply complains again that the directory I've specified is not empty. In a last-ditch effort I tried doing the ebuild config again -- bash-2.05b# ebuild /var/db/pkg/dev-db/postgresql-8.0.1/postgresql-8.0.1.ebuild config * Creating the data directory ... * Initializing the database ... * Postgres 8.0.1 cannot upgrade your existing databases. * You must remove your entire database directory to continue. * (database directory = /var/lib/postgresql). - - - - - - - - - - -- - - - - -- - I figure, I'll do as Gentoo tells me -- bash-2.05b# rm -rf /var/lib/postgresql Now try the ebuild config again -- bash-2.05b# ebuild /var/db/pkg/dev-db/postgresql-8.0.1/postgresql-8.0.1.ebuild config * Creating the data directory ... * Initializing the database ... The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /var/lib/postgresql/data ... ok creating directory /var/lib/postgresql/data/global ... ok creating directory /var/lib/postgresql/data/pg_xlog ... ok creating directory /var/lib/postgresql/data/pg_xlog/archive_status ... ok creating directory /var/lib/postgresql/data/pg_clog ... ok creating directory /var/lib/postgresql/data/pg_subtrans ... ok creating directory /var/lib/postgresql/data/base ... ok creating directory /var/lib/postgresql/data/base/1 ... ok creating directory /var/lib/postgresql/data/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in /var/lib/postgresql/data/base/1 ... ok initializing pg_shadow ... ok enabling unlimited row size for system tables ... ok initializing pg_depend ... ok creating system views ... ok loading pg_description ... ok creating conversions ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /etc/init.d/postgresql start bash-2.05b# /etc/init.d/postgresql start * WARNING: "postgresql" has already been started. bash-2.05b# /etc/init.d/postgresql restart * Restarting PostgreSQL... pg_ctl: PID file "/var/lib/postgresql/data/postmaster.pid" does not exist Is postmaster running? starting postmaster anyway [ ok ] bash-2.05b# So after restarting postgresql, it complains about this missing .pid file and I've seen that problem before. But what the hell I try to do initdb again and it's still a mess -- bash-2.05b$ initdb could not change directory to "/home/floog" The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. initdb: directory "/var/lib/postgresql/data" exists but is not empty If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data". Well things can't get any worse, so I figured I'd try to see if I could make a database -- bash-2.05b$ createdb mytestdb could not change directory to "/home/floog" CREATE DATABASE bash-2.05b$ Supposedly the test database was created but Postgres still wants to use my /home/floog directory for some reason. As Charlie Brown would say --- Ugghh. I'm hoping someone can see what I'm doing wrong. Thankyou for reading such a long post. Mike
On Thu, 2005-02-03 at 13:04 -0600, Rodolfo J. Paiz wrote: > I will guess in advance that I'm missing something obvious, but I > *think* I need a subselect here and I've never done those. Following up on this thread, I've decided that it's impossible to use a subselect for this purpose. Wrong tool. Now investigating joins... For simplicity's sake's, let's say I have a query which returns this: | month | days | |-----------|------| | 2005-01 | 31 | |-----------|------| | 2005-02 | 28 | |-----------|------| | 2005-03 | 31 | |-----------|------| And I have a second query which returns this: | month | flts | |-----------|------| | 2005-01 | 11 | |-----------|------| | 2005-03 | 8 | |-----------|------| Is there a simple way to join those two result sets into one? What I'd like to have is this: | month | days | flts | |-----------|------|------| | 2005-01 | 31 | 11 | |-----------|------|------| | 2005-02 | 28 | 0 | |-----------|------|------| | 2005-03 | 31 | 8 | |-----------|------|------| Since I'm not always able to fly every month, the "flts" result set will not have data for every month. In that case, I'd like to show a zero value as shown above. Can someone point me to the right part of the manual to read? Thanks! Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
----- Original Message ----- From: "Rodolfo J. Paiz" <rpaiz@simpaticus.com> To: <pgsql-novice@postgresql.org> Sent: Friday, February 04, 2005 6:46 PM Subject: Re: [NOVICE] Help with subselect (first time) > On Thu, 2005-02-03 at 13:04 -0600, Rodolfo J. Paiz wrote: >> I will guess in advance that I'm missing something obvious, but I >> *think* I need a subselect here and I've never done those. > > Following up on this thread, I've decided that it's impossible to use a > subselect for this purpose. Wrong tool. Now investigating joins... > > For simplicity's sake's, let's say I have a query which returns this: > > | month | days | > |-----------|------| > | 2005-01 | 31 | > |-----------|------| > | 2005-02 | 28 | > |-----------|------| > | 2005-03 | 31 | > |-----------|------| > > And I have a second query which returns this: > > | month | flts | > |-----------|------| > | 2005-01 | 11 | > |-----------|------| > | 2005-03 | 8 | > |-----------|------| > > Is there a simple way to join those two result sets into one? What I'd > like to have is this: > > | month | days | flts | > |-----------|------|------| > | 2005-01 | 31 | 11 | > |-----------|------|------| > | 2005-02 | 28 | 0 | > |-----------|------|------| > | 2005-03 | 31 | 8 | > |-----------|------|------| > Rodolfo, If these are two tables, called A and B, then doing: select B.month,days,flts from A,B where A.month=B.month; will give you your table. If you need all "months", then using: select B.month,days,flts from B left outer join A on A.month=B.month; However, I doubt that the two outputs you show above are tables, so the two queries here may not be what you want. As for SQL, there are numerous websites (do a google search for SQL tutorial) to learn how to do joins as well as many books, several of which are online. The postgresql documentation has a tutorial section that includes links to a couple of books. Sean
On Fri, 2005-02-04 at 20:43 -0500, Sean Davis wrote: > If these are two tables, called A and B, then doing: > > select B.month,days,flts from A,B where A.month=B.month; > > will give you your table. If you need all "months", then using: > > select B.month,days,flts from B left outer join A on A.month=B.month; > The two "tables" shown above are result sets to which I have a handle. It is my understanding that I can treat them as tables (albeit temporary ones) until they are released. Is that understanding incorrect? Getting all the months listed is *exactly* what I want. I'll try your query a bit later, thanks. > As for SQL, there are numerous websites (do a google search for SQL > tutorial) to learn how to do joins as well as many books, several of which > are online. The postgresql documentation has a tutorial section that > includes links to a couple of books. > Yup, still working through so much documentation it makes my head spin. I've been reading a good hour a day, and a couple of times substantially more than that, since I started this little HTML/PHP/PEAR/PGSQL project. That's why I usually include a phrase like "answers or pointers to relevent docs welcome". Sometimes I haven't yet gotten to the point of knowing which doc to read. Then a URL does wonders for me... RTFM is so much easier when you find the FM. :-) Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
Has any one worked with OLE DB? I will appreciate if some one can copy me a functional OLE DB Connectin String. Best regards, Jorge Perez
On Fri, Feb 04, 2005 at 21:40:29 -0600, "Rodolfo J. Paiz" <rpaiz@simpaticus.com> wrote: > > The two "tables" shown above are result sets to which I have a handle. > It is my understanding that I can treat them as tables (albeit temporary > ones) until they are released. Is that understanding incorrect? Elements of a from item list can be selects. You just put the select query inside parenthesis and use an AS clause to give an alias. Something like: SELECT tb.a FROM (SELECT a FROM ta) AS tb;