8. Aggregates

COUNT Part 1

SELECT Region, COUNT(*) AS "Number of Country"

FROM Country

GROUP BY Region

ORDER BY "Number of Country" DESC, Region ASC

COUNT Part 2

SELECT A.title AS "Album", COUNT(T.track_number) AS "Number of Tracks"

FROM (album A

LEFT JOIN track T ON T.album_id = A.id)

GROUP BY A.title

HAVING "Number of Tracks" >= 10

ORDER BY "Number of Tracks" DESC, "Album" ASC;

Other Aggregate functions

SELECT Region, AVG(Population) AS "AVG", MIN(Population) AS "MIN", MAX(Population) AS "MAX", SUM(Population) AS "SUM"

FROM Country

GROUP BY Region;

Aggregate DISTINCT value

SELECT DISTINCT(GovernmentForm) AS "Government", COUNT(GovernmentForm) AS "Number"

FROM Country

GROUP BY "Government"