SQL Totals Using “With Rollup”When There Is A Null Value

I put in “SQL Totals” into an AI Image Generator. I got AI generated *people* and then also the image above.

Awhile ago I wrote about how to generate totals in SQL. While still relevant, I ran into a specific case that I needed to add more code too it.

I wanted to use version that had WITH ROLLUP. My problem was that I had to LEFT JOIN to another table to display the field needed. If the field was NULL then I had no way to differentiate it from the “Total” row.

The Data

Suppose I have an Employees table, and there’s a NULL value in one of the locations:

SELECT employeeId, employeeName, employeeLoc FROM dbo.Employees

My Locations table:

SELECT location, location_full FROM dbo.Locations

To display the employee’s location, I need to do a LEFT JOIN:

SELECT E.employeeId, E.employeeName, L.location_full
FROM dbo.Employees E
LEFT JOIN dbo.Locations L ON
    E.employeeLoc = L.location

Getting the Total

A simple “Total” in SQL using WITH ROLLUP can be done like this:

SELECT L.location_full, COUNT(*) AS theCount
FROM dbo.Employees E
LEFT JOIN dbo.Locations L ON
    E.employeeLoc = L.location
GROUP BY L.location_full
WITH ROLLUP
ORDER BY L.location_full

The results:

Referring to the previous article where we can convert NULL to say Total, as well as changing the ORDER BY such that it appears at the bottom of the data set:

SELECT ISNULL(L.location_full,'Total') AS location_full, COUNT(*) AS theCount
FROM dbo.Employees E
LEFT JOIN dbo.Locations L ON
 E.employeeLoc = L.location
GROUP BY L.location_full
WITH ROLLUP
ORDER BY 
 CASE WHEN L.location_full IS NULL THEN 99 ELSE 1 END,
 L.location_full

Results:

While it does put “Total” at the bottom, there are two “Totals” and we’re unable to differentiate which is the Total and which is just the NULL value.

Even before replacing NULL with “Total”, the results would have shown two NULL values.

The Solution

We’ll also use the function COALESCE()

SELECT 
   ISNULL( COALESCE(L.location_full,'N/A') ,'Total') AS location_full
   , COUNT(*) AS theCount
FROM dbo.Employees E
LEFT JOIN dbo.Locations L ON
   E.employeeLoc = L.location
GROUP BY  COALESCE(L.location_full,'N/A')
WITH ROLLUP
ORDER BY 
  CASE 
      WHEN COALESCE(L.location_full,'N/A') IS NULL THEN 99
      WHEN COALESCE(L.location_full,'N/A') = 'N/A' THEN 98
      ELSE 1
  END
   , location_full

We can see:

• “Total” is the last item in the output
• “N/A” (the NULL value) is intentionally the second to last item
• Other items are sorted alphabetically