Thread: problem with subselect
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
>
> 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
--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
> |> 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
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
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
willbe 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.*
Thisappears 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