Hi PRA,
Refer the below query.
Input
CREATE TABLE Info(Id INT,FirstName VARCHAR(50),Name VARCHAR(50),Birthday DATETIME,Jeans VARCHAR(50),Profession VARCHAR(50))
INSERT INTO Info VALUES (1, N'Pulodov', N'Rustam', CAST(0x0000901000000000 AS DateTime), N'male', N'pupil')
INSERT INTO Info VALUES (2, N'Asrori', N'Yatim', CAST(0x00008ED000000000 AS DateTime), N'male', N'pupil')
INSERT INTO Info VALUES (3, N'Sharipov', N'Sadriddin', CAST(0x00008BBA00000000 AS DateTime), N'male', N'students')
INSERT INTO Info VALUES (4, N'Soliev', N'Firuz', CAST(0x00007FA100000000 AS DateTime), N'male', N'worker')
INSERT INTO Info VALUES (5, N'Malaev', N'Nurullo', CAST(0x00007F4700000000 AS DateTime), N'male', N'worker')
INSERT INTO Info VALUES (6, N'Sayfurov', N'Karomatullo', CAST(0x00007F7900000000 AS DateTime), N'male', N'worker')
INSERT INTO Info VALUES (7, N'Safarov', N'Muhiddin', CAST(0x00008AB100000000 AS DateTime), N'male', N'students')
INSERT INTO Info VALUES (8, N'Safarova', N'Sabrina', CAST(0x0000801D00000000 AS DateTime), N'male', N'worker')
INSERT INTO Info VALUES (9, N'Safarova', N'Munira', CAST(0x0000900600000000 AS DateTime), N'female', N'pupil')
INSERT INTO Info VALUES (10, N'Nazarova', N'Suman', CAST(0x0000803500000000 AS DateTime), N'female', N'worker')
INSERT INTO Info VALUES (11, N'Gaforova', N'Madina', CAST(0x00008BBE00000000 AS DateTime), N'female', N'Students')
INSERT INTO Info VALUES (12, N'Safarova', N'Munisa', CAST(0x0000917300000000 AS DateTime), N'female', N'pupil')
INSERT INTO Info VALUES (13, N'Safarov', N'Sarabek', CAST(0x0000917300000000 AS DateTime), N'male', N'pupil')
INSERT INTO Info VALUES (14, N'Jabborova', N'Khursheda', CAST(0x00009F3D00000000 AS DateTime), N'female', N'kindergarten')
INSERT INTO Info VALUES (15, N'Jabborov', N'Khurshed', CAST(0x00009F4300000000 AS DateTime), N'male', N'kindergarten')
SQL
SELECT CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(YEAR,BIRTHDAY,GETDATE()))) AS Years
,COUNT( Id) AS [All]
,COUNT(CASE UPPER(JEANS) WHEN 'MALE' THEN 1 END)Male
,COUNT(CASE UPPER(JEANS) WHEN 'FEMALE' THEN 1 END) Female
,COUNT(CASE UPPER(PROFESSION) WHEN 'PUPIL' THEN 1 END) Pupil
,COUNT(CASE UPPER(PROFESSION) WHEN 'STUDENTS' THEN 1 END) Students
,COUNT(CASE UPPER(PROFESSION) WHEN 'WORKER' THEN 1 END) Worker
,COUNT(CASE UPPER(PROFESSION) WHEN 'KINDERGARTEN' THEN 1 END) kindergarten
INTO #Result
FROM INFO
GROUP BY CONVERT(INT,DATEDIFF(YEAR,BIRTHDAY,GETDATE()))
ORDER BY CONVERT(INT,DATEDIFF(YEAR,BIRTHDAY,GETDATE()))
SELECT Years,[All],Male,Female,Pupil,kindergarten
FROM #Result
WHERE Students = 0 AND Worker = 0
UNION ALL
SELECT 'ALL'
,SUM([All])
,SUM(Male)
,SUM(Female)
,SUM(Pupil)
,SUM(kindergarten)
FROM #Result
WHERE Students = 0 AND Worker = 0
SELECT Years,[All],Male,Female,Students,Worker
FROM #Result
WHERE Pupil = 0 AND kindergarten = 0
UNION ALL
SELECT 'ALL'
,SUM([All])
,SUM(Male)
,SUM(Female)
,SUM(Students)
,SUM(Worker)
FROM #Result
WHERE Pupil = 0 AND kindergarten = 0
SELECT 'All sum two table' [All sum two table]
,SUM([All]) [All]
,SUM(Male) Male
,SUM(Female) Female
,SUM(Pupil) + SUM(Students) [Pupil/Students]
,SUM(Worker) + SUM(kindergarten) [Worker/kindergarten]
FROM #Result
DROP TABLE #Result
Output
