0

I have three tables. One with countries, one with regions and one with sets of data for each region. They look like this:

countries
+----+---------+--------------+
| id |  name   | countryValue |
+----+---------+--------------+
|  1 | Germany |            5 |
|  2 | Denmark |            9 |
|    | etc...  |              |
+----+---------+--------------+

regions
+----+-----------+------------------+----------+
| id | fkCountry |       name       |   area   |
+----+-----------+------------------+----------+
|  1 |         1 | Bavaria          | 70549.44 |
|  2 |         1 | Berlin           | 891.85   |
|  3 |         2 | Southern Denmark | 12191    |
|    |           | etc...           |          |
+----+-----------+------------------+----------+

regionData
+----+--------+----------+------------+
| id |  year  | fkRegion | population |
+----+--------+----------+------------+
|  1 | 2014   |        3 |    1203077 |
|  1 | 2015   |        3 |    1206924 |
|    | etc... |          |            |
+----+--------+----------+------------+

I am looking for a query that will allow me to select a list of countries with the total area of that country and the total population based on the latest figures from the regionData table.

Something like this:

+-----------+-------------+-----------+-----------------------+
| countryId | countryName | totalArea | latestPopulationCount |
+-----------+-------------+-----------+-----------------------+
|         1 | Germany     | (value)   | (value)               |
|         2 | Denmark     | (value)   | (value)               |
|           | etc...      |           |                       |
+-----------+-------------+-----------+-----------------------+

I can do a left join that combines the countries and regions tables like this

SELECT countries.*, sum(regions.area) FROM countries LEFT JOIN regions ON countries.id = regions.fkCountry GROUP BY fkCountry

But joining the regionData table to that, so that I only get the sum of the latest values for each region is beyond my capability. All my attempts thus far have ended up in me selecting the combined sum for all years.

I hope you can help me out on this one :)

Regicollis
  • 355
  • 4
  • 13
  • Use an inline view to query region data to only return the max year. Then join it back to your base set to limit by that year. – xQbert Aug 07 '15 at 14:43
  • perhaps something like: `SELECT countries.*, sum(regions.area), R.Population FROM countries LEFT JOIN regions ON countries.id = regions.fkCountry LEFT JOIN (SELECT max(year) mYear, fkRegion from regionData group by fkRegion) B on B.fkRegion=Regions.RegionsId LEFT JOIN RegionData R on R.Year = B.myear and R.fkRegion = B.fkRegion GROUP BY fkCountry` – xQbert Aug 07 '15 at 14:46

0 Answers0