pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with subselect
6+ messages / 4 participants
[nested] [flat]
* problem with subselect
@ 2003-03-07 13:04 Mathieu Arnold <mat@mat.cc>
0 siblings, 1 reply; 6+ messages in thread
From: Mathieu Arnold @ 2003-03-07 13:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have this :
SELECT date_trunc('month', f.date_creation) as mois,
sum(f.montant_ttc / d.taux) as facture,
(SELECT sum(f2.montant_ttc / d2.taux) as facture2
FROM facture AS f2 JOIN devise AS d2 USING (id_devise)
WHERE date_trunc('month', f2.date_creation)
<= date_trunc('month', f.date_creation))
FROM facture AS f JOIN devise AS d USING (id_devise)
GROUP BY date_trunc('month', f.date_creation)
which does not work... telling me that
"Sub-SELECT uses un-GROUPed attribute f.date_creation from outer query"
Which is not really true, but some way is...
What I want is to get the sum of the month, and the sum of the month and
the previous.
If someone has a clue, something, anything... :)
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with subselect
@ 2003-03-07 13:15 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Haller @ 2003-03-07 13:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: m@mat.cc
>
> I have this :
>
> SELECT date_trunc('month', f.date_creation) as mois,
> sum(f.montant_ttc / d.taux) as facture,
> (SELECT sum(f2.montant_ttc / d2.taux) as facture2
> FROM facture AS f2 JOIN devise AS d2 USING (id_devise)
> WHERE date_trunc('month', f2.date_creation)
> <= date_trunc('month', f.date_creation))
> FROM facture AS f JOIN devise AS d USING (id_devise)
> GROUP BY date_trunc('month', f.date_creation)
>
> which does not work... telling me that
>
> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer
query"
> Which is not really true, but some way is...
>
> What I want is to get the sum of the month, and the sum of the month
and
> the previous.
>
> If someone has a clue, something, anything... :)
>
Try
... GROUP BY mois ;
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with subselect
@ 2003-03-07 13:29 Mathieu Arnold <mat@mat.cc>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 6+ messages in thread
From: Mathieu Arnold @ 2003-03-07 13:29 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
--En cette belle journée de vendredi 7 mars 2003 14:15 +0100,
-- Christoph Haller <ch@rodos.fzk.de> écrivait :
|>
|> I have this :
|>
|> SELECT date_trunc('month', f.date_creation) as mois,
|> sum(f.montant_ttc / d.taux) as facture,
|> (SELECT sum(f2.montant_ttc / d2.taux) as facture2
|> FROM facture AS f2 JOIN devise AS d2 USING (id_devise)
|> WHERE date_trunc('month', f2.date_creation)
|> <= date_trunc('month', f.date_creation))
|> FROM facture AS f JOIN devise AS d USING (id_devise)
|> GROUP BY date_trunc('month', f.date_creation)
|>
|> which does not work... telling me that
|>
|> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer
| query"
|> Which is not really true, but some way is...
|>
|> What I want is to get the sum of the month, and the sum of the month
| and
|> the previous.
|>
|> If someone has a clue, something, anything... :)
|>
| Try
| ... GROUP BY mois ;
Already tried, not working either.
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with subselect
@ 2003-03-07 14:51 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 6+ messages in thread
From: Christoph Haller @ 2003-03-07 14:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: mat@mat.cc
> |> SELECT date_trunc('month', f.date_creation) as mois,
> |> sum(f.montant_ttc / d.taux) as facture,
> |> (SELECT sum(f2.montant_ttc / d2.taux) as facture2
> |> FROM facture AS f2 JOIN devise AS d2 USING (id_devise)
> |> WHERE date_trunc('month', f2.date_creation)
> |> <=3D date_trunc('month', f.date_creation))
> |> FROM facture AS f JOIN devise AS d USING (id_devise)
> |> GROUP BY date_trunc('month', f.date_creation)
> |>=20
> |> which does not work... telling me that
> |>=20
> |> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer
> | query"
> |> Which is not really true, but some way is...
> |>=20
> |> What I want is to get the sum of the month, and the sum of the
month
> | and
> |> the previous.
> |>=20
> |> If someone has a clue, something, anything... :)
> |>=20
> | Try
> | ... GROUP BY mois ;
>
> Already tried, not working either.
>
I should have had a closer look:
The WHERE clause is causing the error.
I can't see any quick solution, but what often helped me is
First try to word the query as a view and do the GROUPing then on the
view.
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with subselect
@ 2003-03-07 17:48 Josh Berkus <josh@agliodbs.com>
parent: Mathieu Arnold <mat@mat.cc>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2003-03-07 17:48 UTC (permalink / raw)
To: Mathieu Arnold <m@mat.cc>; pgsql-sql
Mathieu,
> SELECT date_trunc('month', f.date_creation) as mois,
> sum(f.montant_ttc / d.taux) as facture,
> (SELECT sum(f2.montant_ttc / d2.taux) as facture2
> FROM facture AS f2 JOIN devise AS d2 USING (id_devise)
> WHERE date_trunc('month', f2.date_creation)
> <= date_trunc('month', f.date_creation))
> FROM facture AS f JOIN devise AS d USING (id_devise)
> GROUP BY date_trunc('month', f.date_creation)
> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer query"
> Which is not really true, but some way is...
Hmmm ... this looks like a minor parser bug. What Postgres version are you
using?
> What I want is to get the sum of the month, and the sum of the month and
> the previous.
>
> If someone has a clue, something, anything... :)
Sure. Try putting the sub-select in the FROM clause, instead of the SELECT;
that should un-confuse the parser about the grouping.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with subselect
@ 2003-03-07 18:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-03-07 18:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Mathieu Arnold <m@mat.cc>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
>> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer query"
>> Which is not really true, but some way is...
> Hmmm ... this looks like a minor parser bug.
No, it's operating as designed. Note the comments in CVS tip
parse_agg.c:
* NOTE: we recognize grouping expressions in the main query, but only
* grouping Vars in subqueries. For example, this will be rejected,
* although it could be allowed:
* SELECT
* (SELECT x FROM bar where y = (foo.a + foo.b))
* FROM foo
* GROUP BY a + b;
* The difficulty is the need to account for different sublevels_up.
* This appears to require a whole custom version of equal(), which is
* way more pain than the feature seems worth.
Previous versions implemented the check for ungrouped vars a little
differently, but the net effect was the same. (Given that SQL99 allows
only a simple column reference as a GROUP BY element, this isn't a spec
violation, merely a limitation on how far we are willing to extend the
spec.)
There are a number of straightforward ways to rewrite the query to avoid
this, but I can't help wondering whether the basic approach isn't wrong.
The subselect seems an ugly and inefficient way to do it, because it's
re-executing the entire join for each group ... but I can't quite put
my finger on a better way ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-07 18:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 13:04 problem with subselect Mathieu Arnold <mat@mat.cc>
2003-03-07 17:48 ` Josh Berkus <josh@agliodbs.com>
2003-03-07 18:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-07 13:15 Re: problem with subselect Christoph Haller <ch@rodos.fzk.de>
2003-03-07 13:29 ` Mathieu Arnold <mat@mat.cc>
2003-03-07 14:51 Re: problem with subselect Christoph Haller <ch@rodos.fzk.de>
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