Right here we have now two related queries utilizing grouping units
the place the SELECT clause incorporates some expressions calculated in aggregation:

SELECT RN10, RN10 / 10, COUNT FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
               FROM grasp..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

it is plan is right here: first question plan

and

SELECT RN10, SUBSTRING(RN,3,99), COUNT FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
               FROM grasp..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

the corresponding plan is right here: second question plan

Each the queries first calculate some expression for aggregation, RN10 / 10 within the first case and SUBSTRING(RN,3,99) within the second, then the identical expression is used within the SELECT clause however as the primary plan reveals it is re-calculated within the first question and it isn’t within the second.

Because the end result we have now NULLs within the first end result set that’s fairly unexpectedly:

Can somebody clarify why the primary question makes the calculation 2 occasions (one in aggregation and another time within the ultimate choose) whereas the second makes it one time solely?