How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?

Question

I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the results. Right now I'm close to the desired result, but one line exhibits Total and I want that specific line to show Grand Total.

Below is the table created and inserted values in it:

CREATE TABLE Employees
(Id INTEGER IDENTITY(1,1),
 Name VARCHAR(50),
 Gender VARCHAR(50),
 Salary INTEGER,
 Country VARCHAR(50)
)


INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Mark','Male',5000,'USA')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('John','Male',4500,'India')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Pam','Female',5500,'USA')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Sara','Female',4000,'India')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Todd','Male',3500,'India')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Mary','Female',5000,'UK')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Ben','Male',6500,'UK')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Elizabeth','Female',7000,'USA')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Tom','Male',5500,'UK')
INSERT INTO Employees (Name,Gender,Salary,Country)
VALUES('Ron','Male',5000,'USA')

SELECT * FROM Employees

Now I ran the following query:

SELECT COALESCE(Country,'') AS [Country],
CASE 
WHEN GROUPING_ID(Country)=1 
THEN 'Total' 
ELSE COALESCE(Gender,' Grand Total') 
END as [Gender],
SUM(Salary) AS [Total Salary]
FROM Employees 
GROUP BY CUBE(Country,Gender)

And I got the following result:

How can this issue be fixed?

Answer 1

See if this query solves your problem:

SELECT
    ISNULL(Country, 'Total') AS [Country], 
    ISNULL(Gender, 'Grand Total') AS [Gender],
    SUM(Salary) AS [Total Salary]
FROM Employees 
GROUP BY CUBE(Country,Gender);

Reference (in Portuguese): SQL Server – Agrupando dados utilizando ROLLUP, CUBE e GROUPING SETS

Answer 2

I'm afraid I still don't understand the logic you are trying to implement, I'll add an answer because it does not fit in a comment. I'll rephrase it later on if we get to a satisfactory solution.

GROUP BY CUBE(Country, Gender) is syntactic sugar for:

GROUP BY GROUPING SETS ( (Country, Gender)
                       , (Country)
                       , (Gender)
                       , () )

(Country, Gender) is the normal GROUP BY so I believe we can leave that one out of the discussion. The GROUPING SET (Gender) is the Total for each Gender:

USA    GenderTotal 22500
UK     GenderTotal 17000
INDIA  GenderTotal 12000

The GROUPING SET (Country) is the Total for each Country:

CountryTotal Male    30000
CountryTotal Female  21500

and finally, the GROUPING SET () is the Total for all countries and genders:

AllTotal AllTotal    51500

Since your table allow null for country and gender, you are better off using grouping functions instead of COALESCE to determine if it is a grouped row:

Given that, I suspect you want something like:

SELECT
    CASE WHEN GROUPING(Country) = 1 THEN ' CountryTotal' ELSE Country END,
    CASE WHEN GROUPING(Gender) = 1 AND GROUPING(Country) = 1 THEN ' Grand Total' 
         WHEN GROUPING(Gender)= 1 THEN ' GenderTotal'
         ELSE Gender 
    END,
    SUM(Salary) AS [Total Salary]
FROM Employees 
GROUP BY CUBE (Country,Gender)
ORDER BY 1 desc,2 desc;

(No column name)    (No column name)    Total Salary
USA                 Male                10000
USA                 Female              12500
USA                 GenderTotal         22500
UK                  Male                12000
UK                  Female              5000
UK                  GenderTotal         17000
India               Male                8000
India               Female              4000
India               GenderTotal         12000
 CountryTotal       Male                30000
 CountryTotal       Female              21500
 CountryTotal       Grand Total         51500

but I'm not sure why you would like the row:

 CountryTotal   Male    30000

to be presented as:

                TOTAL   30000