title: SQL GROUP BY WITH CUBE
body: <p>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?</p>
  
  <pre><code> 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)
  </code></pre>