SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



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


Id
Name
Gender
Salary
Country


1
Mark
Male
5000
USA

2
John
Male
4500
India

3
Pam
Female
5500
USA

4
Sara
Female
4000
India

5
Todd
Male
3500
India

6
Mary
Female
5000
UK

7
Ben
Male
6500
UK

8
Elizabeth
Female
7000
USA

9
Tom
Male
5500
UK

10
Ron
Male
5000
USA


SELECT 
   COALESCE(Country,'') AS [Country],
   COALESCE(Gender,'Total') AS [Gender],
   SUM(Salary) AS [Total Salary]
FROM Employees 
GROUP BY ROLLUP(Country,Gender)


Country
Gender
Total Salary


India
Female
4000

India
Male
8000

India
Total
12000

UK
Female
5000

UK
Male
12000

UK
Total
17000

USA
Female
12500

USA
Male
10000

USA
Total
22500


Total
51500


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)


Country
Gender
Total Salary
(No column name)
(No column name)


India
Female
4000
0
0

India
Male
8000
0
0

India
Total
12000
0
1

UK
Female
5000
0
0

UK
Male
12000
0
0

UK
Total
17000
0
1

USA
Female
12500
0
0

USA
Male
10000
0
0

USA
Total
22500
0
1


Grand Total
51500
1
1

Id	Name	Gender	Salary	Country
1	Mark	Male	5000	USA
2	John	Male	4500	India
3	Pam	Female	5500	USA
4	Sara	Female	4000	India
5	Todd	Male	3500	India
6	Mary	Female	5000	UK
7	Ben	Male	6500	UK
8	Elizabeth	Female	7000	USA
9	Tom	Male	5500	UK
10	Ron	Male	5000	USA