SQL Order By Custom CASE WHEN ordering

Question

I'm trying to customize the ORDER BY clause so that 'Other Companies' always comes last. I'm not sure what's wrong here. Here's all my code. The WHERE clauses might seem redundant but that's because the parameters are replaced with Macros code where this SQL string gets uploaded to be filled in differently depending on Date and State. Don't worry about that. I've done this before for a different situation where I ordered everything but not with two different columns. The output says that 'AmountOfClaims' in the ORDER BY clause is not a valid column. Can someone help me?

SELECT 
    CASE 
       WHEN co.[CompanyName] != '' 
          THEN co.CompanyName 
          ELSE 'Unspecified Companies' 
    END AS CompanyName, 
    COUNT(co.[CompanyName]) AS [AmountOfClaims]
FROM
    (SELECT 
         CASE 
            WHEN [Claims].[CompanyName] IN (SELECT TOP 10 [Claims].[CompanyName]
                                            FROM [Claims]
                                            WHERE Claims.[HousingRequested] >= '2016-08-02'
                                              AND Claims.[HousingRequested] <= '2016-08-16'
                                              AND (Claims.[State] = NULL OR Claims.[State] >= CASE WHEN NULL IS NULL THEN ' ' ELSE 'ZZZZ' END)
                                            GROUP BY [Claims].[CompanyName]
                                            ORDER BY COUNT([Claims].[CompanyName]) DESC)
               THEN [Claims].[CompanyName] 
               ELSE 'Other Companies' 
         END AS [CompanyName]
     FROM 
         [Claims] 
     WHERE 
         Claims.[HousingRequested] >= '2016-08-02'
         AND Claims.[HousingRequested] <= '2016-08-16'
         AND (Claims.[State] = NULL OR Claims.[State] >= CASE WHEN NULL IS NULL THEN ' ' ELSE 'ZZZZ' END)
    ) AS co
GROUP BY 
    co.[CompanyName]
ORDER BY 
    CASE 
       WHEN [CompanyName] = 'Other Companies' 
          THEN 9999 
          ELSE [AmountOfClaims] 
    END DESC

Is exact your problem. You try to use `[AmountOfClaims]` instead of `COUNT(co.[CompanyName])` that is a `calculated column` but instead of `WHERE` you try to use it on `ORDER BY` — Juan Carlos Oropeza, Aug 16 '16 at 18:55

score 3 · Accepted Answer · answered Aug 16 '16 at 18:31

You can use your current query as a derived table or a CTE, or just use the COUNT in the ORDER BY:

SELECT 
    CASE WHEN co.[CompanyName] != '' THEN co.CompanyName ELSE 'Unspecified Companies' END AS CompanyName, 
    COUNT(co.[CompanyName]) AS [AmountOfClaims]
FROM
    ( SELECT CASE WHEN [Claims].[CompanyName] IN

            ( SELECT TOP 10 [Claims].[CompanyName]
            FROM [Claims]
                WHERE Claims.[HousingRequested] >= '2016-08-02'
                AND Claims.[HousingRequested] <= '2016-08-16'
                AND (Claims.[State] = NULL OR Claims.[State] >= CASE WHEN NULL IS NULL THEN ' ' ELSE 'ZZZZ' END)

            GROUP BY [Claims].[CompanyName]
            ORDER BY COUNT([Claims].[CompanyName]) DESC )

        THEN [Claims].[CompanyName] ELSE 'Other Companies' END AS [CompanyName]

    FROM [Claims] 
        WHERE Claims.[HousingRequested] >= '2016-08-02'
        AND Claims.[HousingRequested] <= '2016-08-16'
        AND (Claims.[State] = NULL OR Claims.[State] >= CASE WHEN NULL IS NULL THEN ' ' ELSE 'ZZZZ' END)
    ) AS co

GROUP BY co.[CompanyName]
ORDER BY CASE WHEN [CompanyName] = 'Other Companies' THEN 9999 ELSE COUNT(co.[CompanyName]) END DESC;

Instead of 9999 I'd use `NULL` so that it always comes last regardless of what `COUNT` you have :) Or even better rewrite that as `ORDER BY CASE WHEN [CompanyName] <> 'Other Companies' THEN COUNT(co.[CompanyName]) END DESC;` — Evaldas Buinauskas, Aug 16 '16 at 18:37
I switched it to -1 because it's descending but yeah that works too. Thanks! — mattz608, Aug 16 '16 at 18:39

SQL Order By Custom CASE WHEN ordering

1 Answers1