SQL Server : “invalid column name” error after GROUP BY on subquery

I am trying to perform the following query on a SQL Server database table with a GROUP BY on a column which results from a CASE statement done on a subquery:

SELECT
    AVG(sales) as avg_sales,
    COUNT(*) as total_sales,
    CASE
        WHEN (pay_method='cc') AND (user_type='subscriber') THEN 'cc-subscribed'
        WHEN (pay_method='cash') AND (user_type='subscriber') THEN 'cash-subscribed'
        ELSE 'standard'
    END as user_payment_type
FROM 
    (SELECT 
         column1, column2,
         UserType as user_type,
         CASE
            WHEN column1='something' AND column2='something_else' THEN 'cc'
            WHEN column1='something_else' AND column2='something' THEN 'cash'
         END as pay_method
    FROM MyTable) b
GROUP BY 
    user_payment_type

The error I am getting is

MSSQLDatabaseException: (207, b”Invalid column name ‘user_payment_type’.DB-Lib error message 20018, severity 16:nGeneral SQL Server error: Check messages from the SQL Servern”)

Note that the column name user_payment_type is unique and does not already exist in MyTable.

Answer

SQL Server does not allow the use of this aliased column in the group by clause (others like MySql do allow it) because the group by clause is executed before select.
You have to use that case statement:

group by CASE
        WHEN (pay_method='cc') AND (user_type='subscriber') THEN 'cc-subscribed'
        WHEN (pay_method='cash') AND (user_type='subscriber') THEN 'cash-subscribed'
        ELSE 'standard'
    END

Advertisement

Answer