If you want to fetch data in like these:
Select count(*)as greater12 from tbl_emp where age>12
Select count(*)as less12 from tbl_emp where age<12
in the one result set, use
UNION command[
^].
SELECT 'Greater then 12' AS [Condition], COUNT([empId]) AS [HowMany]
FROM tbl_emp
WHERE [age] > 12
UNION ALL
SELECT 'Less then 12' AS [Condition], COUNT(empId) AS [HowMany]
FROM tbl_emp
WHERE [age] < 12
Result:
Condition HowMany
Greater then 12 123
Less then 12 85
The second way is to use
CASE[
^]
SELECT [Info], COUNT([Info]) AS [HowMany]
FROM (SELECT [Info] =
CASE
WHEN [age] <= 12 THEN 'Less or equal 12'
WHEN [age] >12 AND [age] < 30 THEN 'Greater then 12 and less then 30'
WHEN [age] >= 30 AND [age] < 50 THEN 'Equal or greater then 30 and less then 50'
WHEN [age] >= 50 THEN 'Equal or greater then 50'
ELSE 'Error!'
END
FROM tbl_emp) AS DT
GROUP BY DT.[Info]
ORDER BY DT.[HowMany]