Nested subqueries in postgresql

Question

I have these three tables

CREATE TABLE "Countries"
(
  name text NOT NULL,
  pop integer,
  CONSTRAINT country PRIMARY KEY (name),
  CONSTRAINT pop_check CHECK (pop > 0)
)

CREATE TABLE "Cities"
( name text NOT NULL, pop integer,
  country_name text,
  CONSTRAINT "City_ref" PRIMARY KEY (name),
  CONSTRAINT country_ref FOREIGN KEY (country_name)
      REFERENCES "db1001002_Countries" (name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "Population" CHECK (pop > 0)
);

CREATE TABLE " Company"
(
  "City_name" text NOT NULL,
  "Company_Name" text NOT NULL,
  no_of_employee integer,
  CONSTRAINT "City_company" PRIMARY KEY ("City_name", "Company_Name"),
  CONSTRAINT city_ref FOREIGN KEY ("City_name")
      REFERENCES "Cities" (name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT employee_check CHECK (no_of_employee > 0)
)

my problem is that I want to use a nested query in the format

select field(s) from table(s)
where field(s) in (select field(s) from table(s) condition)
group by field(s)

to obtain a new table with the fields

country, min_pop, max_pop, average_pop 
for cities with pop > 500000

I tried writing some query but I keep getting errors of too many fields in your sub query. I am using postgresql 9.3.

when I use this query, I get some result.

SELECT country_name, min_pop, max_pop, avg_popo
  FROM (SELECT country_name, min(pop),max(pop),avg(pop)
  FROM
  "Cities"
  GROUP BY country_name)
  AS popcal(country_name,min_pop, max_pop, avg_popo)
  WHERE min_pop>500000

Is there no way of writing the query in this format

select field(s) from table(s)
where field(s) in (select field(s) from table(s) condition)
group by field(s)

?

AndreaBoc · Accepted Answer · 2013-10-25 13:26:44Z

1

I'm not sure I understand the question, but if you neet to filter first Country with at least one city with populations over 50,000 you can use the subquery in this way:

SELECT country_name, MIN(pop) AS min_pop, MAX(pop) AS max_pop, avg(pop) AS avg_pop
FROM "Cities"
  WHERE country_name IN (
    SELECT DISTINCT country_name
    FROM
      "Cities"
    WHERE pop>500000)
GROUP BY country_name

The Subquery returns countries that you need, then you can use it for your aggregate query.

answered Oct 25, 2013 at 13:26

AndreaBoc

3,4392 gold badges19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Nyoagbe Over a year ago

Thank you, that answers my question, but what is the difference between your answer and this one SELECT country_name, min_pop, max_pop, avg_popo FROM (SELECT country_name, min(pop),max(pop),avg(pop) FROM "Cities" GROUP BY country_name) AS popcal(country_name,min_pop, max_pop, avg_popo) WHERE min_pop>500000

AndreaBoc Over a year ago

Your query filter min_pop>500000 after "Cities" aggregation. If You have a City with pop=499999 and a City with pop=5 in the same Country,in your query this Country will be returned.

Community · Accepted Answer · 2017-05-23 12:21:09Z

0

Please edit the original question. Given your comment, you were asking a totally different question... AndreaBoc's answer is correct.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Oct 25, 2013 at 12:48

Leo

1,1461 gold badge7 silver badges20 bronze badges

1 Comment

Michael Nyoagbe Over a year ago

Filipe Silva, I want the query to first check the countries and choose the ones which have at least one city with the population bigger than 500000. So if for example you have Ghana with 5 cities in your database and just one city among this five has a population greater than the 500000 then this part of the query will keep Ghana among the country list. Then in the second part of the query you should calculate the min, max and avg population in this country and it will do the calculation on all five cities of Ghana not just that one city which has a population bigger than the threshold.

Collectives™ on Stack Overflow

Nested subqueries in postgresql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related