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:

Result showing the expected result in red

How can this issue be fixed?

• Lennart - Slava Ukraini: I don't follow what you expect as a result, can you add the expected result?
• pro_data: What I am trying to achieve is that 4th Row and 8th Row of Gender Column Value should display as Total. And the 9th, 10th, 11th and 12th Row of Gender Column Value needs should display 'Grand Total'. I nearly got my desired result with my query but I am not able to display 9th Row of Gender Column Value as 'Grand Total'. I am only trying to fix that. If you look at my result screenshot, I am only trying to replace 'Total' with 'Grand Total' in the 9th Row Value of Gender Column.

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