SQL GROUP BY WITH CUBE

Question

I want to add a total goals column at the end of the table but couldn't manage to do it by CUBE because then it says b.x is invalid. How can I do it?

 DECLARE @Weeks VARCHAR(MAX) = ''
    SELECT @Weeks += (QUOTENAME([Week]) + ',') 
FROM
(Select Distinct [Week] from Fixture
) as z
SET @Weeks = LEFT(@Weeks, LEN(@Weeks) - 1)
print @Weeks

DECLARE @SQL NVARCHAR(MAX) = ''
SET @SQL = '

    select *
    from
    (
    Select Home as Team, [Week] as Weeks, [Home Score] as Goals From Fixture
    group by Home,Week, [Home Score]
    UNION
    Select Away as Team, [Week] as Weeks, [Away Score] as Goals From Fixture
    group by Away,Week, [Away Score]
    ) as A
PIVOT
(
    MAX(Goals)
For Weeks
    IN(' + @Weeks + ')
    )b


'
exec (@SQL)

Answer 1

You can change your inner query somthing like this:-

Select Home as Team, [Week] as Weeks, [Home Score] as Goals From Fixture
group by Home,Week, [Home Score]
UNION ALL
Select Away as Team, [Week] as Weeks, [Away Score] as Goals From Fixture
group by Away,Week, [Away Score]
UNION ALL
Select 'Total', NULL, SUM([Home Score]) + SUM ([Away Score]) as 'Total Goals' From Fixture

This logic might help you.