-2

Recently I tried to create a query to obtain some statistical data, but I got this error:

subquery uses ungrouped column "vegetacionpotencial.ident" from outer query

select 
  veg_pot
  , sum(hauvg)
  ,(
     select 
       sum(hadef) 
     from tematica.vegetacionpotencial v, tematica.deforestacion d 
       join estados on (vegetacionpotencial.ident=estados.ident) 
     where 
       st_covers(v.geom,d.geom) and estado = 'Yucatán'
   ) 
from tematica.vegetacionpotencial
  join estados on (vegetacionpotencial.ident=estados.ident)
where 
  estado = 'Yucatán' 
group by 
  veg_pot
  • Please learn to format your code. Show your table definitions and perhaps go to SQL fiddle and insert minimal data to show us your problem. – Alexandros Oct 20 '15 at 18:43
  • If you don't provide table definitions (which would be best), you must at least table-qualify all columns in use to give us a fighting chance to interpret your query. And please always provide your version of Postgres. Of course, there should also be some explanation as to what the query is supposed to achieve. – Erwin Brandstetter Oct 20 '15 at 21:16
3

Your original query formatted but otherwise unchanged:

SELECT veg_pot, sum(hauvg)
     ,(SELECT sum(hadef) 
       FROM   tematica.vegetacionpotencial v
            , tematica.deforestacion d 
       JOIN   estados ON (vegetacionpotencial.ident = estados.ident) 
       WHERE  st_covers(v.geom, d.geom)
       AND    estado = 'Yucatán') 
FROM   tematica.vegetacionpotencial
JOIN   estados ON (vegetacionpotencial.ident = estados.ident)
WHERE  estado = 'Yucatán' 
GROUP  BY veg_pot;

Since you added the column alias v to tematica.vegetacionpotencial in the correlated subquery, the original table name is not visible any more. To refer3ence the table (like it seems you want to) you must use the visible alias v.

After checking visible identifiers in the inner query, Postgres extends the scope to the next outer query level and finds vegetacionpotencial there. Howev er, the column ident is not grouped in outer query, which leads to the logic error you got:

subquery uses ungrouped column "vegetacionpotencial.ident" from outer query

You can fix the error, but the query is still no good:

SELECT veg_pot, sum(hauvg)
     ,(SELECT sum(hadef) 
       FROM   tematica.vegetacionpotencial v1
       JOIN   estados e ON v1.ident = e.ident
            , tematica.deforestacion d 
       WHERE  st_covers(v.geom, d.geom)
       AND    estado = 'Yucatán') 
FROM   tematica.vegetacionpotencial v
JOIN   estados ON vegetacionpotencial.ident = estados.ident
WHERE  estado = 'Yucatán' 
GROUP  BY veg_pot, v.geom;

Proper information is missing, but going out on a limb, my educated guess is you might want something like this instead:

SELECT veg_pot, sum(hauvg) AS sum_hauvg
    , (SELECT sum(hadef)
       FROM   tematica.deforestacion d 
       WHERE  st_covers(v.geom, d.geom)) AS sum_hadef
FROM   estados e
JOIN   tematica.vegetacionpotencial v USING (ident)
WHERE  e.estado = 'Yucatán'
GROUP  BY veg_pot, v.geom;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.