CODEWITHSUNDEEP
sqlsql-servert-sql
Jim Melcher
Jim Melcher

Reputation: 35

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.

Upvotes: 0

Views: 40

Answers (2)

akwintermute
akwintermute

Reputation: 123

I'm not sure you could do this with ROLLUP, but this is how I would do it:

WITH OrderRows AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY ParentCode ORDER BY SubCode, CompanyName) AS ORD, * FROM @ParentSub
),
AddCountRows AS (
    SELECT ORD, ParentCode, SubCode, CompanyName FROM OrderRows 
    UNION
    -- child counts
    SELECT 
        -- find where to insert this row in the ordered rows above
        (SELECT MAX(ORD) + 1 FROM OrderRows WHERE ParentCode = p.ParentCode) AS ORD, 
        ParentCode, 
        NULL, 
        CAST(COUNT(*) AS varchar(10)) AS CNT 
    FROM @ParentSub p GROUP BY ParentCode
)
-- obtain the data we want and order by the parent and the row order we calculated
SELECT ParentCode AS Parent, SubCode AS Subsidiary, CompanyName AS Name FROM AddCountRows ORDER BY ParentCode, ORD

Upvotes: 0

Charlieface
Charlieface

Reputation: 71578

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)
);

Upvotes: 1

Related Questions