With Rollup Is Only Producing Nulls

This is what the table looks like 'normally' WorkloadCategory | WorkloadCapacity| WorkloadTotalTime -----------------|-----------------|------------------ DI | 317632

Solution 1:

Your WITH ROLLUP isn't working because you've told the query to GROUP on each of the number fields as categories but you've not told it how to SUM the total. Further, WITH ROLLUP will roll up in each of the 4 fields but that's not what you want. You are after only a total at the end (and maybe a total per category) so with GROUPING SETS you can tell it what you want.

Something like this:

SELECT
    wlc.WorkloadCategory,
    SUM(wl.WorkloadCapacity) AS WorkloadCapacity,
    SUM(ROUND(wl.WorkloadMinutes * wl.WorkloadCapacity /60/ assum.WorkYearHours, 2)) AS WorkloadTotalTime
FROM
    swam.Assumptions assum
CROSSJOIN 
    swam.WorkloadCategories  wlc
INNERJOIN 
    swam.Workloads wl ON wlc.WorkloadCategoryID = wl.WorkloadCategoryID
GROUPBYGROUPING SETS (
  (wlc.WorkloadCategory, wl.WorkloadCapacity, assum.WorkYearHours, wl.WorkloadMinutes),
  (wlc.WorkloadCategory),
  ()
)