0

I am getting a "missing right parenthesis" error message for the below code; I don't understand why.

select region_name, avg(population) as atlagos_lakossag
from world_regions, countries 
where world_regions.region_id = countries.region_id group by region_name
minus
select region_name, min(lol) as atlagos_lakossag 
from 
(select region_name, avg(population) as lol from countries, world_regions 
where world_regions.region_id = countries.region_id group by region_name);
Jancsoro
  • 31
  • 3
  • You're not selecting anything... i.e. you don't have an upper layer of your SELECT statement. Write `select * from ( )`. You can remove some of the additional brackets encapsulating your current SELECT statements. P.S. the explicit JOIN has been around for a couple of decades. It can stop a lot of mistakes early and is worth using... – Ben Nov 23 '14 at 22:02
  • i tried it now, i got the same error. – Jancsoro Nov 23 '14 at 22:05
  • Well, you're also missing a GROUP BY REGION_NAME in the outer select of the bottom part of the minus and your ORDER BY in the top-part is redundant. You haven't exactly given everyone a lot to work with here... what are you trying to do? What's the purpose? Do you have any sample data? what is the _exact_ error message? It looks like you're trying to get all the average populations per region, save for lowest. If that's the case window functions, for instance [like this](http://stackoverflow.com/a/26442640/458741) would possibly be better and certainly be shorter. – Ben Nov 23 '14 at 22:08
  • i have two tables world_region(region_id, region_name) and countries(coutry_id, region_id, countri_name, capitol, population, currency_code) i have to write a select, witch enumerates the region names and average population except the region where the average population is the lowest – Jancsoro Nov 23 '14 at 22:17
  • 1
    @Ben: no "upper" layer is required when using `minus` (the same way you don't need an "upper" layer when using `union`). But I agree that removing the unnecessary parentheses would be a first step to make the query more readable. –  Nov 23 '14 at 22:24
  • i removed the unnecessary parentheses, and the error changed to: ORA-00937: not a single-group group function (i edit the code upper) – Jancsoro Nov 23 '14 at 22:31
  • Adding a group by to your lowerpart would fixit... but Your min() in lower part has no meaning, as you already grouped with region_name. – Maheswaran Ravisankar Nov 23 '14 at 22:51

0 Answers0