mw112009 (2/10/2016)
Jeff M:Please refer to Post #1760015.
I changed the code to include 2 extra rows in table @a that are not in table @B.
So you definitely have to use both tables. Can't get away with using just table @B.
Anyway, whenever you have time, I'd like to see how to use the ROLLUP
WITH ROLLUP is sort of equivalent to the GROUPING SETS syntax used in Luis' code. WITH ROLLUP is deprecated; the GROUPING SETS is more versatile and is the recommended syntax going forward.
You can adapt the query posted by Luis to use an outer join between your two tables. Run it without a GROUP BY and you will see that now the two additional rows from @a are retained. Add back the GROUP BY and you will see them duplicated. As I already wrote in my reply to Jeff, there are ways to fix that, but they are rather convoluted.