WITH CTE_Pivot AS (
SELECT
State,
[Junior],
[Senior],
[Junior]+[Senior] AS [All Comps],
[Cricket Blast],
[Daughters & Dads],
[Pop-Up Blast],
[School Holiday],
[WWCF],
[Cricket Blast]+[Daughters & Dads]+[Daughters & Dads]+[Pop-Up Blast]+[School Holiday]+[WWCF] as [All Program]
FROM (
SELECT
distinct
unique_id,
State,
CASE
WHEN program IN (‘CRICKET_BLAST’,’Master Blaster’,’ALL_GIRLS_CRICKET_BLAST’,’Junior Blaster’) THEN ‘Cricket Blast’
WHEN program LIKE ‘%Daughters and Dads%’ THEN ‘Daughters & Dads’
WHEN program LIKE ‘%Pop Up Program%’ THEN ‘Pop-Up Blast’
WHEN program LIKE ‘%Holiday Program%’ THEN ‘School Holiday’
WHEN program LIKE ‘%WWCF%’ THEN ‘WWCF’
END AS Program,
CASE
WHEN ProgramCategory LIKE ‘%Junior Cricket%’ THEN ‘Junior’
WHEN ProgramCategory LIKE ‘%Senior Cricket%’ THEN ‘Senior’
END AS ProgramCategory
FROM [DMP].[FCT_CC_PlayersRegistrationEngagementStatus]
WHERE state IS NOT NULL
) g
PIVOT (
COUNT(unique_id) FOR
Program IN ([Cricket Blast], [Daughters & Dads], [Pop-Up Blast], [School Holiday], [WWCF])
) AS pvt1

PIVOT (
    COUNT(unique_id) FOR
        ProgramCategory IN ([Junior], [Senior])
) AS pvt2

)
SELECT * FROM CTE_Pivot

UNION ALL

SELECT
‘Total’ AS State,
SUM([Junior]) AS [Junior],
SUM([Senior]) AS [Senior],
SUM ([All Comps]) AS [All Comps],
SUM([Cricket Blast]) AS [Cricket Blast],
SUM([Daughters & Dads]) AS [Daughters & Dads],
SUM([Pop-Up Blast]) AS [Pop-Up Blast],
SUM([School Holiday]) AS [School Holiday],
SUM([WWCF]) AS [WWCF],
SUM([All Program]) AS[All Program]
FROM CTE_Pivot;

getting error : Invalid column name ‘unique_id’.