Custom SQL Order By Statement

Isn't it enough to just add an order by?
order by datediff(YYYY,birth_date,getdate())

Otherwise, maybe you could also change the age categories to "30 and under" and "65 and above"

Edit This query may be easier / less repetitive. By the way, the BETWEEN clause is inclusive, so you should use < 30 instead of <= 30 (and > 65 instead of >= 65) to make sure those ages aren't counted twice.

SELECT 'Under 30' AS 'Age Category',
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) < 30 and gender ='f' and status ='a' and member_type ='mm') AS 'Female',
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) < 30 and gender ='m' and status ='a' and member_type ='mm') AS 'Male'
UNION ALL
SELECT '30-39',
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 30 AND 39 and gender ='f' and status ='a' and member_type ='mm')
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 30 AND 39 and gender ='m' and status ='a' and member_type ='mm')
UNION ALL
SELECT '40-49',
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 40 AND 49 and gender ='f' and status ='a' and member_type ='mm')
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 40 AND 49 and gender ='m' and status ='a' and member_type ='mm')
UNION ALL
SELECT '50-65',
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 50 AND 65 and gender ='f' and status ='a' and member_type ='mm')
    (select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) BETWEEN 50 AND 65 and gender ='m' and status ='a' and member_type ='mm')
UNION ALL
SELECT 'Over 65',
(select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) > 65 and gender ='f' and status ='a' and member_type ='mm')
(select count(*) from name n1 where datediff(YYYY,n1.birth_date,getdate()) > 65 and gender ='m' and status ='a' and member_type ='mm')

If you create a table to join on this becomes so much easier.

CREATE TABLE dim_age_band (
  id            INT,
  title         VARCHAR(32),
  ordinal       INT,
  bound_lower   INT,
  bound_upper   INT
)

INSERT INTO dim_age_band SELECT 1, 'Under 30', 1,  0,   30;
INSERT INTO dim_age_band SELECT 2, '30-39',    2, 30,   40;
INSERT INTO dim_age_band SELECT 3, '40-49',    3, 40,   50;
INSERT INTO dim_age_band SELECT 4, '50-65',    4, 50,   66;
INSERT INTO dim_age_band SELECT 5, 'Over 65',  5, 66, 1000;

SELECT
  MAX([dim_age_band].[name]                            )   AS age_band_name,
  SUM(CASE WHEN [name].[gender] = 'f' THEN 1 ELSE 0 END)   AS female,
  SUM(CASE WHEN [name].[gender] = 'm' THEN 1 ELSE 0 END)   AS male
FROM
  dim_age_band
LEFT JOIN
  name
    ON  [name].[birth_date] <= DATEADD(YYYY, GetDate(), [dim_age_band].[bound_lower])
    AND [name].[birth_date] >  DATEADD(YYYY, GetDate(), [dim_age_band].[bound_Upper])
    AND [name].[STATUS]      = 'A'
    AND [name].MEMBER_TYPE  IN ('MM')
GROUP BY
  [dim_age_band].[id]
ORDER BY
  MAX([dim_age_band].[ordinal])
;

EDIT :

Note that I'm also not using DATEDIFF() on the [birth_date] field. Instead I'm running that calculation on today's date and the age band boundaries. This means that it becomes possible to use indexes to search the [name] table by the [birth_date]; if such indexes exist.

EDIT :

CTE Version...

WITH
  dim_age_band (name, ordinal, bound_lower, bound_upper)
AS
(
            SELECT 'Under 30', 1,  0,   30
  UNION ALL SELECT '30-39',    2, 30,   40
  UNION ALL SELECT '40-49',    3, 40,   50
  UNION ALL SELECT '50-65',    4, 50,   66
  UNION ALL SELECT 'Over 65',  5, 66, 1000
)
SELECT
  MAX([dim_age_band].[name]                            )   AS age_band_name,
  SUM(CASE WHEN [name].[gender] = 'f' THEN 1 ELSE 0 END)   AS female,
  SUM(CASE WHEN [name].[gender] = 'm' THEN 1 ELSE 0 END)   AS male
FROM
  dim_age_band
LEFT JOIN
  name
    ON  [name].[birth_date] <= DATEADD(YYYY, GetDate(), [dim_age_band].[bound_lower])
    AND [name].[birth_date] >  DATEADD(YYYY, GetDate(), [dim_age_band].[bound_Upper])
    AND [name].[STATUS]      = 'A'
    AND [name].MEMBER_TYPE  IN ('MM')
GROUP BY
  [dim_age_band].[id]
ORDER BY
  MAX([dim_age_band].[ordinal])
;

Inline View version:

SELECT
  MAX([dim_age_band].[name]                            )   AS age_band_name,
  SUM(CASE WHEN [name].[gender] = 'f' THEN 1 ELSE 0 END)   AS female,
  SUM(CASE WHEN [name].[gender] = 'm' THEN 1 ELSE 0 END)   AS male
FROM
(
            SELECT 'Under 30' AS name, 1 AS ordinal,  0 AS bound_lower,   30 AS bound_upper
  UNION ALL SELECT '30-39'    AS name, 2 AS ordinal, 30 AS bound_lower,   40 AS bound_upper
  UNION ALL SELECT '40-49'    AS name, 3 AS ordinal, 40 AS bound_lower,   50 AS bound_upper
  UNION ALL SELECT '50-65'    AS name, 4 AS ordinal, 50 AS bound_lower,   66 AS bound_upper
  UNION ALL SELECT 'Over 65'  AS name, 5 AS ordinal, 66 AS bound_lower, 1000 AS bound_upper
)
  AS dim_age_band
LEFT JOIN
  name
    ON  [name].[birth_date] <= DATEADD(YYYY, GetDate(), [dim_age_band].[bound_lower])
    AND [name].[birth_date] >  DATEADD(YYYY, GetDate(), [dim_age_band].[bound_Upper])
    AND [name].[STATUS]      = 'A'
    AND [name].MEMBER_TYPE  IN ('MM')
GROUP BY
  [dim_age_band].[id]
ORDER BY
  MAX([dim_age_band].[ordinal])
;