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 |