Group By - Incorrect syntax near 'mycolumn' [closed]

Question

I'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, '')))  Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);

Error message: Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'Address4'

I can't see what is wrong - this works:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);

What is the syntax error?

Answer 1

Figured it out shortly after I posted. I need to remove the alias from the GROUP BY clause.

Solution:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))),
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);