Styjun

I'm getting the error thatROLLUP is not a function name but the documentation says it should work
Msg 104162, Level 16, State 1, Line 2
'ROLLUP' is not a recognized built-in function name.

I've tried group by grouping sets but it told me the syntax was wrong, that's when I saw that grouping sets doesn't work with DW

SELECT S.[ProjectID]
 ,P.ProjectId
 ,P.Level2
 ,S.[PTDIncurredAmount]
 ,S.[PriorYearIncurredAmount]
 ,S.[YTDIncurredAmount], sum([YTDIncurredAmount]) as CTDActuals
 FROM [Fact].[vProjectSummary] as S
 JOIN dim.vProject as P on S.ProjectID=P.ProjectId
 Group by ROLLUP (P.Level2, S.ProjectID )

If anyone can point me to more specifically the problem with this code for Azure SQL DW I'd greatly appreciate it!

According to this github documentation ROLLUP is not supported:

The GROUP BY T-SQL clause aggregates data to a summary set of rows.
GROUP BY has some options that SQL Data Warehouse does not support.
These options have workarounds.

These options are

GROUP BY with ROLLUP

GROUPING SETS

GROUP BY with CUBE

The workaround suggested is to use UNION ALL to simulate the ROLLUP. If I take a simplified version of your query

SELECT S.projectID, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
 INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectId
GROUP BY ROLLUP ( P.Level2, S.ProjectID )
ORDER BY 1, 2, 3

By using ROLLUP this query requests the following aggregations:

• Level2 and ProjectId


• Level2


• Grand total

You can simulate this with UNION ALL:

-- Level 2 and ProjectID
SELECT S.ProjectID, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
 INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID
GROUP BY S.ProjectID, p.Level2

UNION ALL

-- Level 2 
SELECT NULL, p.Level2, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
 INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID
GROUP BY p.Level2

UNION ALL

-- Grand total
SELECT NULL, NULL, SUM( [YTDIncurredAmount] )
FROM [Fact].[vProjectSummary] AS S
 INNER JOIN dim.vProject AS P ON S.ProjectID = P.ProjectID

I could not get any queries using ROLLUP to work although this link does seem to suggest it's possible:

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

There is also a feedback item you can vote for here:

https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/35836048-grouping-and-group-by-rollup-functions-needs-to-be