Subtotal of child records without COUNT?

ParentSub Table:

ParentCode	SubCode	CompanyName
MEGA1	MEGA1	Megacorp
MEGA1	SUBC1	Subsidiary Company
MEGA1	EASC1	East Coast Operations
MEGA1	EURC1	Europe Company
BIGLY	ALPH1	Alphabet Company
BIGLY	BIGLY	Big Development
HOTS1	HOTS1	Hot Dog Company

I want:

Parent	Subsidiary	Name
MEGA1	MEGA1	Megacorp
MEGA1	SUBC1	Subsidiary Company
MEGA1	EASC1	East Coast Operations
MEGA1	EURC1	Europe Company
MEGA1	NULL	4
BIGLY	ALPH1	Alphabet Company
BIGLY	BIGLY	Big Development
BIGLY	NULL	2

Surely there is a way to make ROLLUP do this?

SELECT ParentCode, SubCode, CompanyName
FROM ParentSub
GROUP BY ParentCode WITH ROLLUP 

I don’t want the COUNTs in the rows and I don’t want them in Aggregate functions until the group changes.

Answer

GROUPING SETS might be better than ROLLUP here, as you only want 2 levels of rollup and you miss out other levels.

We can also use GROUPING() to check if a column has been aggregated. Note that COUNT needs to be converted to varchar to hold it in the same column

SELECT
    ParentCode,
    SubCode,
    CASE WHEN GROUPING(CompanyName) = 1
      THEN CAST(COUNT(*) AS varchar(10))
      ELSE CompanyName END
     AS CompanyName
FROM ParentSub
GROUP BY GROUPING SETS (
    (ParentCode, SubCode, CompanyName),
    (ParentCode)
);