Skip to content
Advertisement

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

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

enter image description here

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:

enter image description here

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.

Advertisement

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:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement