If you want to count users based on age category, you have to create helper-table to define age categories. Then you'll be able to join data from
users
table. Have a look at example:
DECLARE @users TABLE(UserId INT IDENTITY(1,1), UName VARCHAR(30), BirthDate DATE)
INSERT INTO @users(UName, BirthDate)
VALUES('A', '1995-02-02'), ('B', '1994-03-03'), ('C', '1993-04-04'),
('D', '1987-05-05'), ('G', '1986-06-06'), ('J', '1987-07-07'),
('E', '1985-08-08'), ('H', '1969-09-09'), ('K', '1968-10-10'),
('F', '1969-11-11'), ('I', '1959-12-12'), ('L', '1951-01-02')
DECLARE @AgeCat TABLE (AgeCatId INT IDENTITY(1,1), AgeFrom INT, AgeTo INT)
INSERT INTO @AgeCat (AgeFrom, AgeTo)
VALUES(18, 20), (21, 42), (43, 67)
DECLARE @curDate DATE = GETDATE()
SELECT t2.AgeCatId, t2.AgeFrom, t2.AgeTo, COUNT(t1.UserId) AS CountOfUsers
FROM (
SELECT us.UserId, us.UName, DATEDIFF(YY,us.BirthDate, @curDate) AS Age
FROM @users AS us
) AS t1 INNER JOIN @AgeCat AS t2 ON t1.Age BETWEEN t2.AgeFrom AND t2.AgeTo
GROUP BY t2.AgeCatId, t2.AgeFrom, t2.AgeTo
Result:
AgeCatId AgeFrom AgeTo CountOfUsers
1 18 20 1
2 21 42 6
3 43 67 5
Got it?