SELECT
Name,
CountryCode,
Min(Population) over (PARTITION BY CountryCode) min_population,
Max(Population) over (PARTITION BY CountryCode) max_population,
AVG(Population) over p_code avg_population,
SUM(Population) over p_code sum_population,
COUNT(Population) over p_code count_population
FROM city
WINDOW p_code AS (PARTITION BY CountryCode)
| Name |
CountryCode |
min_population |
max_population |
avg_population |
sum_population |
count_population |
| Oranjestad |
ABW |
29,034 |
29,034 |
29,034.00 |
29,034 |
1 |
| Mazar-e-Sharif |
AFG |
127,800 |
1,780,000 |
583,025.00 |
2,332,100 |
4 |
| Herat |
AFG |
127,800 |
1,780,000 |
583,025.00 |
2,332,100 |
4 |
| Qandahar |
AFG |
127,800 |
1,780,000 |
583,025.00 |
2,332,100 |
4 |
| Kabul |
AFG |
127,800 |
1,780,000 |
583,025.00 |
2,332,100 |
4 |
| Luanda |
AGO |
118,200 |
2,022,000 |
512,320.00 |
2,561,600 |
5 |
| Namibe |
AGO |
118,200 |
2,022,000 |
512,320.00 |
2,561,600 |
5 |
| Benguela |
AGO |
118,200 |
2,022,000 |
512,320.00 |
2,561,600 |
5 |
| Lobito |
AGO |
118,200 |
2,022,000 |
512,320.00 |
2,561,600 |
5 |
| ... |
| Window functions can be written either in the SELECT clause or in a separate WINDOW clause, where the window is given an alias that can be accessed in the SELECT clause. |