How to replace the NULL value of the last row of a column with 'Grand total' while retaini-CodePudding

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],
    COALESCE(Gender, 'Total') AS [Gender],
    SUM(Salary) AS [Total Salary]
FROM
    Employees 
GROUP BY 
    ROLLUP(Country, Gender)

When you look at the query result, the last row of the Gender column has the value 'Total' in it.

I want to replace 'Total' with 'Grand Total' only in the last row of Gender column while keeping 'Total' text in the other rows of Gender column.

Is there any possibility to achieve that ?

If so, then what is the simplest possible way to achieve it ?

CodePudding user response：

You can use GROUPING_ID() for it:

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

DBFIDDLE

EDIT: In the comment of the question is noted that the order of the result should be specified, to make sure it is correct.

This query can be ordered like this, to make sure totals are below the details.

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

CodePudding user response：

One other easy way would be to just to concatenate the country name using isnull which is preferable in Sql server with just two values, such as:

select 
  isnull(Country,'') Country, 
  isnull(Gender, Concat(IsNull(Country, 'Grand'), ' Total')) Gender,
    Sum(Salary) [Total Salary]
from Employees 
group by rollup(Country,Gender);