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:

games

yr	city	continent
2000	Sydney	Australasia
2004	Athens	Europe
2008	Beijing	Asia
2019	London	Europe
2016	india	asia
2018	indonesia	asia
2014	england	Europe

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');

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

• SUM and COUNT
• SELECT WHERE