GROUP BY

Understanding GROUP BY

GROUP BY divides query results into groups based on the values of specified variables. Each group becomes a single row in the output, with aggregate functions computing values across the items in each group.

Any non-aggregated variable in SELECT must appear in GROUP BY. This ensures each result row corresponds to exactly one group.

Basic GROUP BY Examples

SELECT ?type ?typeLabel (COUNT(?item) AS ?count)
WHERE {
  ?item wdt:P31 ?type .
  VALUES ?type { wd:Q5 wd:Q6256 wd:Q515 wd:Q7889 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?type ?typeLabel

This query counts items of four different types: humans (Q5), countries (Q6256), cities (Q515), and video games (Q7889). The VALUES clause limits which types we examine, and GROUP BY ensures we get one count per type.

SELECT ?continent ?continentLabel (COUNT(?country) AS ?countryCount)
WHERE {
  ?country wdt:P31 wd:Q6256 ;
           wdt:P30 ?continent .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?continent ?continentLabel
ORDER BY DESC(?countryCount)

Here we group countries by their continent (P30). The result shows how many countries exist on each continent, sorted from most to least.

Grouping by Multiple Variables

SELECT ?author ?authorLabel ?workType ?workTypeLabel (COUNT(?work) AS ?count)
WHERE {
  VALUES ?author { wd:Q5879 wd:Q1067 wd:Q7836 }
  ?work wdt:P50 ?author ;
        wdt:P31 ?workType .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?author ?authorLabel ?workType ?workTypeLabel
ORDER BY ?authorLabel DESC(?count)

This query examines works by three famous authors: Johann Wolfgang von Goethe (Q5879), Gabriel García Márquez (Q1067), and Friedrich Nietzsche (Q7836). Grouping by both author and work type reveals what kinds of works each author created.

GROUP BY with Multiple Aggregates

SELECT ?continent ?continentLabel
  (COUNT(?country) AS ?countryCount)
  (SUM(?population) AS ?totalPop)
  (AVG(?population) AS ?avgPop)
WHERE {
  ?country wdt:P31 wd:Q6256 ;
           wdt:P30 ?continent ;
           wdt:P1082 ?population .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?continent ?continentLabel
ORDER BY DESC(?totalPop)

You can use multiple aggregate functions in a single query. This example calculates country count, total population, and average population for each continent in one query.

Key Entities Used in Examples