What is the key to mastering speed cubing

Game of Hierarchies - How to Master the GROUP BY Clauses

It is easiest for me to stay focused on a topic when I have a specific goal in mind. Therefore, we will start with a (hopefully for you) challenging report, which we will approach step-by-step towards completion.

Figure 3 shows us exactly this report. In this report we do not list the individual POIs and limit ourselves to the regions, continents and world levels. The gap Surname lists us the names of the regions and continents. In the case of the continents, we want not only the name of the continent, but also the text "* SUBTOTAL"with display. We proceed in the same way with the world with"** TOTAL". In the column POICounter we show the number of POIs in these regions and continents and also a total.

The naive approach to getting a sum per level would be the following statement:

SELECT
POIName,
RegionName,
ContinentName,
WorldName,
count (*) as CountOfSomething
FROM
dbo.GoT_POI;

However, this is acknowledged with the following error message:

Msg 8120, Level 16, State 1, Line 42
Column 'dbo.GoT_POI.POIName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The error message criticizes POINamethat it is neither in an aggregate function nor in a GROUP BYClause occurs. We definitely don't want to put the name in an aggregate function (although sometimes it helps to use a MIN or MAX - the explanation is beyond the scope of this article). So only that remains GROUP BY-Clause. And we have to put all the other columns in these as well:

SELECT
POIName,
RegionName,
ContinentName,
WorldName,
count (*) as CountOfSomething
FROM
dbo.GoT_POI
GROUP BY
POIName,
RegionName,
ContinentName,
WorldName

ORDER BY
WorldName,
ContinentName,
RegionName,
POIName;

In most cases where I group by columns, it also makes sense to sort by those columns. This is not syntactically required by SQL, but a list in which the regions are jumbled up and not sorted by continents is rather difficult to read, especially if the continents show subtotals.

And to be honest: In the list of the most common mistakes I make in everyday work with SQL, there is forgetting the GROUP BY (right after forgetting to select the correct database) right at the top. From my point of view, it's just that annoying GROUP BY-Clause to be specified. Because if I don't use an aggregation function on a column, then I want to group by this column. It's a shame that the ANSI institute doesn't have any connect-items where I could put this as a desired feature.