0

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?

2
  • I don't follow what you expect as a result, can you add the expected result? Mar 28, 2022 at 19:27
  • 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.
    – pro_data
    Mar 28, 2022 at 19:33

2 Answers 2

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.