Refer the below query.
SQL
DECLARE @Test AS TABLE(Id INT,FirstName VARCHAR(50),Name VARCHAR(50),Birthday DATETIME,Regional INT)
INSERT INTO @Test VALUES(1,'Pulodov','Rustam','12.22.1987',1)
INSERT INTO @Test VALUES(2,'Asrori','Yatim','02.06.1978',1)
INSERT INTO @Test VALUES(3,'Sharipov','Sadriddin','12.08.1991',1)
INSERT INTO @Test VALUES(4,'Soliev','Firuz','06.16.1989',1)
INSERT INTO @Test VALUES(5,'Malaev','Nurullo','03.18.1986',2)
INSERT INTO @Test VALUES(6,'Sayfurov','Karomatullo','05.07.1980',2)
INSERT INTO @Test VALUES(7,'Safarov','Muhiddin','03.18.1988',2)
INSERT INTO @Test VALUES(8,'Safarov','Sarabek','10.18.1983',2)
SELECT Regional
,COUNT(Regional) AS [All]
,SUM(CASE WHEN CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)) < 28 THEN 1 ELSE 0 END) AS [Till 28 years]
,SUM(CASE WHEN CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)) >=28 THEN 1 ELSE 0 END) AS [Big from 28 years]
FROM @Test
GROUP BY Regional
Output
| Regional |
All |
Till 28 years |
Big from 28 years |
| 1 |
4 |
2 |
2 |
| 2 |
4 |
0 |
4 |
