cdrblx cdrblx - 3 months ago 10
SQL Question

SQL Order By Custom CASE WHEN ordering

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

Answer

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;
Comments