-- *** before: ***
-- a value computed over the whole group needed a correlated subquery
SELECT
    c.Name,
    c.Population,
    (SELECT AVG(Population) FROM city WHERE CountryCode =
     c.CountryCode) AS AvgPop
FROM city c

-- *** in version 2003: ***
SELECT
    Name,
    Population,
    AVG(Population) OVER (PARTITION BY CountryCode) AS AvgPop,
    RANK() OVER (
                 PARTITION BY CountryCode ORDER BY Population DESC) AS PlaceInCountry
FROM city

-- Support: Oracle since 8i, MS SQL Server 2005 (ranking) and 2012
-- (aggregates with a frame), PostgreSQL 8.4, Firebird 3.0,
-- SQLite 3.25 and MySQL 8.0. Access has no window functions.