Another very good question!
Manifold 9 departs here from the standard SQL that Fehily uses throughout his book-and also from Manifold 8.
The departure is (a) partly because of the way Manifold 9 SQL is wired (which has its own good reasons), and (b) partly because Manifold 9 SQL does not need a construction like the above, which is, let's face it, artificial and convoluted.
Take point (b) first. (Point (a) will follow in a second post.) Why is there a CASE expression in the SELECT list that matches verbatim the CASE expression in the GROUP BY expression?
Because the general rule is, that in a query with GROUPing, each expression in the SELECT list either must have been used to define a GROUP, else must be enclosed within an aggregate function in the SELECT.
(The rule does not also run the other way. That is, an expression can be used for GROUPing, without necessarily appearing in the SELECT list. That's fine, though in practice, the match will run both ways, as in Fehily's query above.)
At first glance, this looks wasteful. In the query above, it looks as though the same CASE expression will be evaluated twice. In fact an SQL parser will normally detect the exact match, and "look through" the SELECT to the GROUP, evaluating any matching expressions only once. It doesn't look efficient, but it normally is.
We discussed this in a Radian Studio beta thread, not for CASE expressions but for expressions generally.
Adam's comments there were valuable and some are worth repeating here.
First, he pointed out that the requirement to match expressions exactly, if they appear in both SELECT and GROUP BY, is
a hack. It relies on the expressions in the SELECT list and the GROUP list coinciding from the point of view of the parser, not logically.
Example log from SQL Server:
select a, sum(b) from t group by a
select a+1, sum(b) from t group by a+1
select a+1, sum(b) from t group by 1+a
-- fails with an error, the expressions are logically the same but the parser thinks they are different (because it does not have the right tools to reason about them, they come in later phases)
select a+1, sum(b) from t group by a+1+0
-- fails for the same reason
You get the point. I hadn't thought about this before Adam pointed it out.
OK, a hack, but is it necessary? Not any more. Adam again (same post):
It does not feel right to *have* to repeat the same - possibly complex - expression in two places basically via copy and paste, because anything else might scare the parser. We think it is better to write the expression once and then refer to it by name. This whole thing with allowing expressions in GROUP as long as they coincide exactly with expressions in the SELECT list exists solely as a relic from those times when nobody could do nested SELECTs.
The part I have put in bold sums up the whole matter nicely, and also points to what we should do, to write a query that does not rely on a hack.
We should use a subquery (a nested SELECT).
So to rewrite Fehily's query for Manifold 9 SQL (this should equally work in Manifold 8, if single quotes are changed to double quotes):
COUNT(*) AS [Num titles]
WHEN [sales] IS NULL
WHEN [sales] <= 1000
THEN 'Not more than 1,000'
WHEN [sales] <= 10000
THEN 'Between 1,001 and 10,000'
WHEN [sales] <= 100000
THEN 'Between 10,001 and 100,000'
WHEN [sales] <= 1000000
THEN 'Between 100,001 and 1,000,000'
ELSE 'Over 1,000,000'
END AS [Sales category]
GROUP BY [Sales category]
ORDER BY MIN(sales) ASC
[For anyone following along, we are looking at listing 6.15 in Fehily, SQL Database Programming, p.175 in the print edition, location 3992 in Kindle.--@dyalsjas, it would be nice to the listing or section number with Fehily questions when you think it makes sense.]