Skip to content
Advertisement

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.

Advertisement

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)
);
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement