ORDER BY the Items Within a Case Statement

Question

Is there another way I can sort the results by the Age Group appropriately? As a work-around, I placed a character before each age group to display in chronological order - but if the letter is not there, then it does not display in the order I am expecting. Here is the T-SQL:

WITH AgeData
AS ( SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - CASE WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
THEN 1
ELSE 0
END AS [Age]
FROM   dbo.Customers ) ,
 GroupAge
AS ( SELECT [Age] ,
        CASE WHEN AGE < 4 THEN 'a0 - 3'
             WHEN AGE BETWEEN 4 AND 8 THEN 'b4 - 8'
             WHEN AGE BETWEEN 9 AND 12 THEN 'c9 - 12'
             WHEN AGE BETWEEN 13 AND 17 THEN 'd13 - 17'
             WHEN AGE BETWEEN 18 AND 22 THEN 'e18 - 22'
             WHEN AGE BETWEEN 23 AND 26 THEN 'f23 - 26'
             WHEN AGE BETWEEN 27 AND 33 THEN 'g27 - 33'
             WHEN AGE BETWEEN 34 AND 40 THEN 'h34 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN 'i41 - 50'
             WHEN AGE BETWEEN 51 AND 60 THEN 'j51 - 60'
             WHEN AGE BETWEEN 61 AND 65 THEN 'k61 - 65'
             WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
             WHEN AGE > 75 THEN 'm75+'
             ELSE 'nInvalid Birthdate'
        END AS [AgeGroups]
 FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
     [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups]
ORDER BY GroupAge.[AgeGroups];

Without the character such as 'a', 'b', 'c', etc... my result set looks like:

If possible, I'd like to sort correctly without the work-around of using a letter.

Answer 1

You could add one more column to order item

WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) ,
GroupAge
AS (       SELECT [Age] ,
      CASE WHEN AGE < 4 THEN '0 - 3'
          WHEN AGE BETWEEN 4 AND 8 THEN '4 - 8'
          WHEN AGE BETWEEN 9 AND 12 THEN '9 - 12'
          WHEN AGE BETWEEN 13 AND 17 THEN '13 - 17'
          WHEN AGE BETWEEN 18 AND 22 THEN '18 - 22'
          WHEN AGE BETWEEN 23 AND 26 THEN '23 - 26'
          WHEN AGE BETWEEN 27 AND 33 THEN '27 - 33'
          WHEN AGE BETWEEN 34 AND 40 THEN '34 - 40'
          WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
          WHEN AGE BETWEEN 51 AND 60 THEN '51 - 60'
          WHEN AGE BETWEEN 61 AND 65 THEN '61 - 65'
          WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
          WHEN AGE > 75 THEN 'm75+'
          ELSE 'nInvalid Birthdate'
      END AS [AgeGroups],
      CASE WHEN AGE < 4 THEN 1
          WHEN AGE BETWEEN 4 AND 8 THEN 2
          WHEN AGE BETWEEN 9 AND 12 THEN 3
          WHEN AGE BETWEEN 13 AND 17 THEN 4
          WHEN AGE BETWEEN 18 AND 22 THEN 5
          WHEN AGE BETWEEN 23 AND 26 THEN 6
          WHEN AGE BETWEEN 27 AND 33 THEN 7
          WHEN AGE BETWEEN 34 AND 40 THEN 8
          WHEN AGE BETWEEN 41 AND 50 THEN 9
          WHEN AGE BETWEEN 51 AND 60 THEN 10
          WHEN AGE BETWEEN 61 AND 65 THEN 11
          WHEN AGE BETWEEN 66 AND 74 THEN 12
          WHEN AGE > 75 THEN 13
          ELSE 14
      END AS [AgeGroupId]
FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
      [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups],[AgeGroupId]
ORDER BY GroupAge.[AgeGroupId]

Another solution: Use a temp table that contains group information

DECLARE @GroupAge AS TABLE
(
   GroupID int,
   StartAge int,
   EndAge int,
   GroupName AS CONCAT(StartAge, '-', EndAge)
)

INSERT INTO @GroupAge
(
    GroupID,
    StartAge,
    EndAge
)
VALUES (1,0,3) -- insert all groups you need

;WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) 
SELECT   COUNT(*) AS [AgeGroupCount] ,
         ga.GroupName
FROM     AgeData a
INNER JOIN @GroupAge ga ON ( a.Age BETWEEN ga.StartAge AND ga.EndAge)
GROUP BY ga.GroupID, ga.GroupName
Order By ga.GroupID