Can't using alias into GROUP BY clause

Question

I have this join into large query

LEFT JOIN --RIVA MICRO
    (SELECT 
         TT.DATAAREAID, TT.VOUCHER,MA.MAINACCOUNTID AS AD,
         SUM(TT.TAXBASEAMOUNT) AS TAXBASEAMOUNT,
         SUM(TT.TAXAMOUNT) AS TAXAMOUNT 
     FROM 
         TAXTRANS TT
     INNER MERGE JOIN 
         TAXTRANSDETAIL_BE MA ON TT.RECID = MA.RECID
     WHERE 
         TT.TRANSDATE <= @FECHA AND ("+@Codigo_RIVA+")
     GROUP BY 
         TT.DATAAREAID, TT.VOUCHER, MA.MAINACCOUNTID) AS RIVA2 ON Asiento.Asiento = RIVA2.VOUCHER 
                                                               AND Asiento.Cuenta = RIVA2.AD
                                                               AND Asiento.Empresa = RIVA2.DATAAREAID

It works correctly but now I implement a temporary table, so my query is like:

LEFT JOIN 
    (SELECT 
         DATAAREAID, VOUCHER, MAINACCOUNTID,
         SUM(TAXBASEAMOUNT) AS TAXBASEAMOUNT, SUM(TAXAMOUNT)  
     FROM 
         ##TTM TT 
     WHERE 
        (TT.TAXCODE LIKE 'RIVA11DPT' OR  TT.TAXCODE LIKE 'RIVA16DPT' 
         OR  TT.TAXCODE LIKE 'RIVA16T')
     GROUP BY  
         TT.TAXAMOUNT, TT.TAXBASEAMOUNT, TT.TAXCODE, TT.DATAAREAID, 
         TT.VOUCHER, TT.MAINACCOUNTID) AS RIVA2 ON Asiento.Asiento = RIVA2.VOUCHER 
                                                AND Asiento.Cuenta = RIVA2.MAINACCOUNTID
                                                AND Asiento.Empresa = RIVA2.DATAAREAID

But now problem is into GROUP BY CLAUSE, I´m getting into alias AS RIVA2

Incorrect syntax near the keyword 'AS'.

Someone knows why it occurs?. Regards

Possible duplicate of [SQL - using alias in Group By](https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by) — Adam, Oct 17 '17 at 00:45
I checked that link before, but that´s not the problem, I already update my question to be more clarify @Adam — Pepe, Oct 17 '17 at 00:46
Could you post the entire query, please? Using only this part of the query, it is hard to tell if why you are getting this problem. — wizard07KSU, Oct 17 '17 at 00:58

Gordon Linoff · Accepted Answer · 2017-10-17T00:52:00.147

2

I think you intend:

LEFT JOIN 
(SELECT TT.DATAAREAID, TT.VOUCHER, TT.MAINACCOUNTID,
        SUM(TT.TAXBASEAMOUNT) AS TAXBASEAMOUNT, SUM(TT.TAXAMOUNT)  
 FROM ##TTM TT 
 WHERE TT.TAXCODE IN ('RIVA11DPT', 'RIVA16DPT', 'RIVA16T')
 GROUP BY TT.DATAAREAID, TT.VOUCHER, TT.MAINACCOUNTID
) RIVA2   
ON Asiento.Asiento = RIVA2.VOUCHER AND
   Asiento.Cuenta = RIVA2.MAINACCOUNTID AND
   Asiento.Empresa = RIVA2.DATAAREAID

Notes:

You only need to aggregate by the unaggregated columns in the GROUP BY.
It is well known that you cannot use column aliases defined in a SELECT in most other clauses associated with that SELECT.
IN is much simpler than a collection of OR clauses.
You should give SUM(TT.TAXAMOUNT) a name.

edited Oct 17 '17 at 00:52

answered Oct 17 '17 at 00:48

Gordon Linoff

1,242,037
58
646
786

what do you change, I can see you encapsule my where use IN, but what more you change? – Pepe Oct 17 '17 at 00:51
@Pepe . .. Look at the `GROUP BY`. – Gordon Linoff Oct 17 '17 at 00:52

Can't using alias into GROUP BY clause

1 Answers1