pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComplex query
8+ messages / 6 participants
[nested] [flat]
* Complex query
@ 2000-08-27 23:42 J. Fernando Moyano <txinete@wanadoo.es>
0 siblings, 0 replies; 8+ messages in thread
From: J. Fernando Moyano @ 2000-08-27 23:42 UTC (permalink / raw)
To: pgsql-sql@hub.org
Hey everybody !!!
I am new on this list !!!
I have a little problem .....
I try this on my system:
"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantidad)"
I get this result:
ERROR: rewrite: comparision of 2 aggregate
columns not supported
but if a try this one:
"select rp.n_lote from relpedidos rp, relfacturas rf where
rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)"
It's OK !!
What's up???
Do you think i found a bug ???
Do exist some limitation like this in subqueries??
(Perhaps Postgres don't accept using aggregates in subqueries ???)
I tried this too:
"select n_lote from pedidos where n_lote not in (select rp.n_lote from
relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote
having sum(rp.cantidad)=sum(rf.cantidad))"
but the result was the same !
And i get the same error message (or similar) when i try other variations.
Thanks !!!
Fer
--
************* ****** ****** ********** ***** ***** *******
************* ***** ****** ********** ****** ***** ***********
***** ******** **** ************* **** ****
***** **** **** ***** ******* **** ****
***** ******* **** ***** ****** **** ****
***** ****** ***** ********* ***** ***** ************
***** ****** ****** ********* ***** ***** ********
(*) SymeX ==> http://www.lantik.com
(*) Web en http://www.arrakis.es/~txino
(*) Informate sobre LINUX en http://www.linux.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* complex query
@ 2003-03-03 10:59 Matt Gerginski <mattgerg@users.sourceforge.net>
0 siblings, 1 reply; 8+ messages in thread
From: Matt Gerginski @ 2003-03-03 10:59 UTC (permalink / raw)
To: pgsql-sql
I have two tables, users and options. The common element between the
tables is "username". I want to select the "email" from "user" but only
if the "mailing_list" option is set to true in the "options" table.
Here are the tables:
select username, email from users;
username | email
---------------+--------------------------------
joe | joe@yahoo.com
heidi | heidi@localhost
payday | brant@localhost
fake | fake@localhost
mattgerg | mattgerg@users.sourceforge.net
god | god@heaven.org
select username, mailing_list from options;
username | mailing_list
---------------+--------------
payday | t
god | t
fake | t
mattgerg | t
I want to write a query that will return the emails of only the users
payday, god, fake, and mattgerg.
Is this at all possible? I am new to sql, and I am having trouble.
--Matt
--
Matt Gerginski <mattgerg@users.sourceforge.net>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: complex query
@ 2003-03-03 17:18 Victor Yegorov <viy@pirmabanka.lv>
parent: Matt Gerginski <mattgerg@users.sourceforge.net>
0 siblings, 0 replies; 8+ messages in thread
From: Victor Yegorov @ 2003-03-03 17:18 UTC (permalink / raw)
To: pgsql-sql
* Matt Gerginski <mattgerg@users.sourceforge.net> [03.03.2003 19:11]:
> I have two tables, users and options. The common element between the
> tables is "username". I want to select the "email" from "user" but only
> if the "mailing_list" option is set to true in the "options" table.
>
> Here are the tables:
>
> select username, email from users;
> username | email
> ---------------+--------------------------------
> joe | joe@yahoo.com
> heidi | heidi@localhost
> payday | brant@localhost
> fake | fake@localhost
> mattgerg | mattgerg@users.sourceforge.net
> god | god@heaven.org
>
> select username, mailing_list from options;
> username | mailing_list
> ---------------+--------------
> payday | t
> god | t
> fake | t
> mattgerg | t
>
>
> I want to write a query that will return the emails of only the users
> payday, god, fake, and mattgerg.
>
> Is this at all possible? I am new to sql, and I am having trouble.
>
select u.username, u.email from users as u, options as o where u.username =
o.username and o.mailing_list = true;
You'd better read some book on SQL.
--
Victor Yegorov
^ permalink raw reply [nested|flat] 8+ messages in thread
* complex query
@ 2012-10-28 00:01 Mark Fenbers <mark.fenbers@noaa.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Mark Fenbers @ 2012-10-28 00:01 UTC (permalink / raw)
To: pgsql-sql
This is a multi-part message in MIME format.
--------------030503080902050804050401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFCC" text="#000000">
I have a query:<br>
SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true
GROUP BY id;<br>
<br>
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.<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) FROM mytable WHERE
condition2 = true) FROM mytable WHERE condition1 = true GROUP BY id;<br>
<br>
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.<br>
<br>
Is there a better way to go about this?<br>
<br>
Thanks for any help you provide.<br>
Mark<br>
<br>
</body>
</html>
--------------030503080902050804050401
Content-Type: text/x-vcard; charset=utf-8;
name="mark_fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="mark_fenbers.vcf"
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
--------------030503080902050804050401--
Attachments:
[text/x-vcard] mark_fenbers.vcf (345B, ../../508C75D1.2070906@noaa.gov/2-mark_fenbers.vcf)
download | inline:
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: complex query
@ 2012-10-28 00:24 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mark Fenbers <mark.fenbers@noaa.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2012-10-28 00:24 UTC (permalink / raw)
To: Mark Fenbers <mark.fenbers@noaa.gov>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: complex query
@ 2012-10-28 01:56 Mark Fenbers <mark.fenbers@noaa.gov>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mark Fenbers @ 2012-10-28 01:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
This is a multi-part message in MIME format.
--------------030209020201040806020004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFCC" text="#000000">
<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>
</body>
</html>
--------------030209020201040806020004
Content-Type: text/x-vcard; charset=utf-8;
name="mark_fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="mark_fenbers.vcf"
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
--------------030209020201040806020004--
Attachments:
[text/x-vcard] mark_fenbers.vcf (345B, ../../508C90B5.3060308@noaa.gov/2-mark_fenbers.vcf)
download | inline:
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: complex query
@ 2012-10-28 02:20 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mark Fenbers <mark.fenbers@noaa.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2012-10-28 02:20 UTC (permalink / raw)
To: Mark Fenbers <mark.fenbers@noaa.gov>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: complex query
@ 2012-10-28 11:15 Oliveiros d'Azevedo Cristina <oliveiros.cristina@asperger-talents.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-10-28 11:15 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Mark Fenbers <mark.fenbers@noaa.gov>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-10-28 11:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-27 23:42 Complex query J. Fernando Moyano <txinete@wanadoo.es>
2003-03-03 10:59 complex query Matt Gerginski <mattgerg@users.sourceforge.net>
2003-03-03 17:18 ` Re: complex query Victor Yegorov <viy@pirmabanka.lv>
2012-10-28 00:01 complex query Mark Fenbers <mark.fenbers@noaa.gov>
2012-10-28 00:24 ` Re: complex query Scott Marlowe <scott.marlowe@gmail.com>
2012-10-28 01:56 ` Re: complex query Mark Fenbers <mark.fenbers@noaa.gov>
2012-10-28 02:20 ` Re: complex query Scott Marlowe <scott.marlowe@gmail.com>
2012-10-28 11:15 ` Re: complex query Oliveiros d'Azevedo Cristina <oliveiros.cristina@asperger-talents.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox