-- *** before: ***
-- the same subquery had to be repeated in every place it was needed
SELECT country.Name, t.CityPop
FROM country
JOIN (SELECT CountryCode, SUM(Population) AS CityPop
      FROM city GROUP BY CountryCode) AS t
  ON t.CountryCode = country.Code

-- *** in version 1999: ***
WITH CityTotal AS (
    SELECT CountryCode, SUM(Population) AS CityPop
    FROM city
    GROUP BY CountryCode
)
SELECT country.Name, CityTotal.CityPop
FROM country
JOIN CityTotal ON CityTotal.CountryCode = country.Code

-- Support: Oracle 9i (it calls them subquery factoring), MS SQL Server
-- 2005, Firebird 2.1, PostgreSQL 8.4, SQLite 3.8.3 and MySQL 8.0.
-- Access has no WITH clause at all.