SELECT .. GROUP BY
SELECT .. GROUP BY
Host cities and continents for the Olympics Games are stored in the table games
.
Notice that Europe appears in the table twice:
yr | city | continent |
---|---|---|
2000 | Sydney | Australasia |
2004 | Athens | Europe |
2008 | Beijing | Asia |
2012 | London | Europe |
2016 | Rio | South America |
schema:scott
DROP TABLE games
CREATE TABLE games(
yr INTEGER,
city VARCHAR(20),
continent VARCHAR(20));
INSERT INTO games VALUES (2000,'Sydney','Australasia');
INSERT INTO games VALUES (2004,'Athens','Europe');
INSERT INTO games VALUES (2008,'Beijing','Asia');
INSERT INTO games VALUES (2012,'London','Europe');
INSERT INTO games VALUES (2016,'Rio','South America');
In a GROUP BY statement only distinct values are shown for the column in the GROUP BY. This example shows the continents hosting the Olympics with the count of the number of games held.
SELECT continent, COUNT(yr) FROM scott.games
GROUP BY continent
SELECT continent, COUNT(yr) FROM games
GROUP BY continent
See also