Thread: complex query
I have a query:<br /> SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true GROUP BY id;<br /><br /> Thisgives me 3 columns, but what I want is 5 columns where the next two columns -- SUM(col3), SUM(col4) -- have a slightlydifferent WHERE clause, i.e., WHERE condition2 = true.<br /><br /> I know that I can do this in the following way:<br/> SELECT id, SUM(col1), SUM(col2), (SELECT SUM(col3) FROM mytable WHERE condition2 = true), (SELECT SUM(col4) FROMmytable WHERE condition2 = true) FROM mytable WHERE condition1 = true GROUP BY id;<br /><br /> Now this doesn't seemto bad, but the truth is that condition1 and condition2 are both rather lengthy and complicated and my table is ratherlarge, and since embedded SELECTs can only return 1 column, I have to repeat the exact query in the next SELECT (exceptfor using "col4" instead of "col3"). I could use UNION to simplify, except that UNION will return 2 rows, and thecode that receives my resultset is only expecting 1 row.<br /><br /> Is there a better way to go about this?<br /><br/> Thanks for any help you provide.<br /> Mark<br /><br />
On Sat, Oct 27, 2012 at 6:01 PM, Mark Fenbers <mark.fenbers@noaa.gov> wrote: > I have a query: > SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true GROUP > BY id; > > This gives me 3 columns, but what I want is 5 columns where the next two > columns -- SUM(col3), SUM(col4) -- have a slightly different WHERE clause, > i.e., WHERE condition2 = true. > > I know that I can do this in the following way: > SELECT id, SUM(col1), SUM(col2), (SELECT SUM(col3) FROM mytable WHERE > condition2 = true), (SELECT SUM(col4) FROM mytable WHERE condition2 = true) > FROM mytable WHERE condition1 = true GROUP BY id; > > Now this doesn't seem to bad, but the truth is that condition1 and > condition2 are both rather lengthy and complicated and my table is rather > large, and since embedded SELECTs can only return 1 column, I have to repeat > the exact query in the next SELECT (except for using "col4" instead of > "col3"). I could use UNION to simplify, except that UNION will return 2 > rows, and the code that receives my resultset is only expecting 1 row. > > Is there a better way to go about this? I'd do somethings like: select * from ( select id, sum(col1), sum(col2) from tablename group by yada ) as a [full, left, right, outer] join ( select id, sum(col3), sum(col4) from tablename group by bada ) as b on (a.id=b.id); and choose the join type as appropriate.
<blockquote cite="mid:CAOR=d=17g1mnKENbJDTiKb-7_JptrdSE43Lz=WF+XmNL9R1akw@mail.gmail.com" type="cite"><pre wrap=""> I'd do somethings like: select * from ( select id, sum(col1), sum(col2) from tablename group by yada ) as a [full, left, right, outer] join ( select id, sum(col3), sum(col4) from tablename group by bada ) as b on (a.id=b.id); and choose the join type as appropriate.</pre></blockquote> Thanks! Your idea worked like a champ!<br /> Mark<br /><br />
On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers <mark.fenbers@noaa.gov> wrote: > I'd do somethings like: > > select * from ( > select id, sum(col1), sum(col2) from tablename group by yada > ) as a [full, left, right, outer] join ( > select id, sum(col3), sum(col4) from tablename group by bada > ) as b > on (a.id=b.id); > > and choose the join type as appropriate. > > Thanks! Your idea worked like a champ! > Mark The basic rules for mushing together data sets is to join them to put the pieces of data into the same row (horiztonally extending the set) and use unions to pile the rows one on top of the other. One of the best things about PostgreSQL is that it's very efficient at making these kinds of queries efficient and fast. I've written 5 or 6 page multi-join multi-union queries that still ran in hundreds of milliseconds, returning thousands of rows.
Hi, Scott. I'd like to kick in this thread to ask you some advice, as you are experienced in optimizing queries. I also use extensively joins and unions (less than joins though). Anyway, my response times are somewhat behind miliseconds, they are situated on seconds range, and sometimes they exceed one minute. I have some giant tables with over 100 000 000 records collected for more than 6 years. Most of my queries are made over recent data, so I'm considering partitioning the tables. But I believe that my problem arises from misplaced indexes... I have an index on every PRK. But if the join is not made using the PRKs, perhaps, should I place an index also on the joined columns? The application is not a hard real time one, but if you can do it much faster than I do, then I'm positive that I must have been doin something wrong. Could you please let me know about your thoughts on this? Thanks in advance Best, Oliver ----- Original Message ----- From: "Scott Marlowe" <scott.marlowe@gmail.com> To: "Mark Fenbers" <mark.fenbers@noaa.gov> Cc: <pgsql-sql@postgresql.org> Sent: Sunday, October 28, 2012 2:20 AM Subject: Re: [SQL] complex query > On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers <mark.fenbers@noaa.gov> > wrote: >> I'd do somethings like: >> >> select * from ( >> select id, sum(col1), sum(col2) from tablename group by yada >> ) as a [full, left, right, outer] join ( >> select id, sum(col3), sum(col4) from tablename group by bada >> ) as b >> on (a.id=b.id); >> >> and choose the join type as appropriate. >> >> Thanks! Your idea worked like a champ! >> Mark > > The basic rules for mushing together data sets is to join them to put > the pieces of data into the same row (horiztonally extending the set) > and use unions to pile the rows one on top of the other. > > One of the best things about PostgreSQL is that it's very efficient at > making these kinds of queries efficient and fast. I've written 5 or 6 > page multi-join multi-union queries that still ran in hundreds of > milliseconds, returning thousands of rows. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >