How do I restrict my SQL aggregation Rollups to a specific grouping?

The following SQL returns 5 grouped columns and two aggregated columns:

select ten.TenancyName,
       svd.EmployeeId,
       usr.DisplayName,
       tjt.JobTitle,
       tjc.Name as JobCategory,
       count(svd.ratingpoints) as NumReviews, 
       avg(svd.ratingpoints) as Rating 
  from surveydetails svd
  join AbpUsers usr on usr.Id = svd.EmployeeId
  join AbpTenants ten on ten.Id = usr.TenantId
  join TenantJobTitle tjt on tjt.TenantId = usr.TenantId and tjt.Id = usr.JobTitleId
  join TenantJobTitleCategories tjc on tjc.Id = tjt.JobTitleCategory
 where svd.employeeid is not null
   and svd.CreationTime > '2020-01-01'
   and svd.CreationTime < '2021-12-31'
 group by ten.TenancyName,
          rollup(svd.EmployeeId,
                 usr.DisplayName,
                 tjt.JobTitle,
                 tjc.Name)
order by ten.TenancyName,
         svd.EmployeeId,
         usr.DisplayName,
         tjt.JobTitle,
         tjc.[Name]

I do want the rollup to the level of TenancyName, but it don’t need all the other intermediate rollup lines. In fact, you can see that rolling up from the Doctor’s (Employee’s) row up to the EmployeeId produces the exact same value on every row because these are one-to-one data attributes. The only level where it makes sense to roll up to is the TenancyName level because there are multiple Doctors within each Tenant.

After the fact, I can eliminate the unwanted rows either using a HAVING clause or by making this a sub-select to an outer select which will filter out the undesired rows. For example:

   select ten.TenancyName,
      svd.EmployeeId,
      usr.DisplayName,
      tjt.JobTitle,
      tjc.Name as JobCategory,
      count(svd.ratingpoints) as NumReviews, 
      avg(svd.ratingpoints) as Rating 
 from surveydetails svd
 join AbpUsers usr on usr.Id = svd.EmployeeId
 join AbpTenants ten on ten.Id = usr.TenantId
 join TenantJobTitle tjt on tjt.TenantId = usr.TenantId and tjt.Id = usr.JobTitleId
 join TenantJobTitleCategories tjc on tjc.Id = tjt.JobTitleCategory
where svd.employeeid is not null
  and svd.CreationTime > '2020-01-01'
  and svd.CreationTime < '2021-12-31'
group by ten.TenancyName,
         rollup(svd.EmployeeId,
                usr.DisplayName,
                tjt.JobTitle,
                tjc.Name)
having (svd.EmployeeId is null and 
        usr.DisplayName is null and 
        tjt.JobTitle is null and 
        tjc.Name is null)
       or
       (ten.TenancyName is not null and 
        svd.EmployeeId is not null and 
        usr.DisplayName is not null and 
        tjt.JobTitle is not null and 
        tjc.Name is not null)
order by ten.TenancyName,
         svd.EmployeeId,
         usr.DisplayName,
         tjt.JobTitle,
         tjc.[Name]

This delivers what I want, but if this can be done naturally via the group by / rollup construct I should think that would be preferable from both simplicity and performance standpoints.

Answer

Most databases that support rollup also support grouping sets. I prefer grouping sets because it gives you explicit control over the groups.

For instance, if you wanted to group by all the columns, tenancy, and have a total row, you can use:

group by grouping sets ( (ten.TenancyName, svd.EmployeeId, usr.DisplayName, tjt.JobTitle, tjc.Name),
                         (ten.TenancyName),
                         ()
                       )

Advertisement

Answer