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

Question

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 Server\n")

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

Possible duplicate of [SQL - using alias in Group By](https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by) — GSerg, Jun 22 '19 at 08:59

score 2 · Accepted Answer · answered Jun 22 '19 at 08:54

2

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

answered Jun 22 '19 at 08:54

forpas

160,666
10
38
76

I'm impressed, had no idea that was the case. Thank you so much forpas! Also, little ot: any suggestion on how could I do to reuse that `CASE` statement so to have a cleaner code and not duplicate all that both in the `SELECT` and `GROUP BY`? – crash Jun 22 '19 at 08:58
Only if you can put it in a subquery or a cte and select it from the subquery. As it is right now you can't. – forpas Jun 22 '19 at 09:00
Yep thanks, I think Lukasz provided an example for that! – crash Jun 22 '19 at 09:00

score 1 · Answer 2 · answered Jun 22 '19 at 08:57

Your SELECT and GROUP BY should match. You could avoid duplicating code by using CROSS APPLY:

WITH cte AS (
  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
)
SELECT  AVG(c.sales) as avg_sales,
        COUNT(*) as total_sales,
        s.user_payment_type
FROM  cte c
CROSS APPLY (SELECT 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) s(user_payment_type)
GROUP BY s.user_payment_type

Hey Lukasz, thanks I just asked this question to forpas as well! I think I'm going to accept his answer as he was the first one (not sure how to deal with these cases though). Thanks a lot for your feedback — crash, Jun 22 '19 at 08:59

Mureinik · Answer 3 · 2019-06-22T09:16:20.067

As others have noted, you can't reference column aliases in the group by clause, but should reference the same expression there too.

Note, however, that you're performing two calculations on the same data. You could perform both calculations in the same subquery to make the query shorter and easier to maintain:

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

score 0 · Answer 4 · answered Jun 22 '19 at 11:59

A simple way to do this without nested subqueries uses apply:

SELECT v1.user_payment_type,
       AVG(t.sales) as avg_sales,
       COUNT(*) as total_sales
FROM MyTable t CROSS APPLY
     (VALUES (CASE WHEN t.column1 = 'something' AND t.column2 = 'something_else' THEN 'cc'
                   WHEN t.column1 = 'something_else' AND t.column2 = 'something' THEN 'cash'
              END
             )
     ) v(pay_method) CROSS APPLY
     (VALUES (CASE WHEN v.pay_method = 'cc' AND t.user_type = 'subscriber' THEN 'cc-subscribed'
                   WHEN v.pay_method = 'cash' AND t.user_type = 'subscriber' THEN 'cash-subscribed'
                   ELSE 'standard'
              END)
     ) v1(user_payment_type)
GROUP BY v1.user_payment_type;

This allows you to define interdependent definitions without nesting subqueries or CTEs or repeating definitions.

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

4 Answers4