CODEWITHSUNDEEP
sql-servert-sqlgroup-byalias
Tiago
Tiago

Reputation: 365

Alias unavailable in GROUP BY

I have to group the BaseIncidencia column (sum the values), but it is unavailable. As said in SQL Server AS statement aliased column within WHERE statement it would be simple, but i don't have a column name to refer to. Only the alias.

BEGIN
SELECT @Artigo AS Artigo, T1.Contrib AS ContribID, YEAR(T1.Data) AS Ano, T3.DescricaoMes, CAST(ROUND(SUM(T1.Debito/0.23),2) AS decimal(16,2)) BaseIncidencia,
       CAST(ROUND(SUM(T1.Debito),2) AS decimal(16,2)) AS IVA, (CASE WHEN T5.Valid = 'True' THEN 'VALIDO' WHEN T5.Name = '---' THEN 'INEXISTENTE' ELSE 'INVALIDO' END) AS 'ValidadeNIF'
FROM  
#CONTAS_INI AS T1  
LEFT JOIN  
db.dbo.VatCheck T5 
ON T1.ContribuinteID = T5.VatNumber 
LEFT JOIN
#Meses AS T3
ON MONTH(T1.Data) = T3.Mes
GROUP BY T1.CntID, T1.ContribuinteID, YEAR(T1.Data), (CASE WHEN T5.Valid = 'True' THEN 'VALIDO' 
         WHEN T5.Name = '---' THEN 'INEXISTENTE' ELSE 'INVALIDO' END), T3.DescricaoMes
ORDER BY (CASE WHEN T5.Valid = 'True' THEN 'VALIDO' WHEN T5.Name = '---' THEN 'INEXISTENTE' ELSE 'INVALIDO' END), T1.ContribuinteID
END

I tried replacing the following line:

GROUP BY T1.CntID, T1.ContribuinteID, YEAR(T1.Data), (CASE WHEN T5.Valid = 'True' THEN 'VALIDO'

With:

GROUP BY T1.CntID, T1.ContribuinteID, YEAR(T1.Data), CAST(ROUND(SUM(T1.Debito/0.23),2) AS decimal(16,2)), (CASE WHEN T5.Valid = 'True' THEN 'VALIDO'

Only to get the Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. error.

So my question is, why i can't include BaseIncidencia alias in GROUP BY? And what's the workaround?

Upvotes: 0

Views: 868

Answers (3)

KtX2SkD
KtX2SkD

Reputation: 752

Reposting 12th Dec.'s comment as an answer.

You'll probably want to wrap your original SELECT ... FROM ... with SELECT * FROM ( /*your original half*/ ) AS T, leaving the GROUP ... ORDER ... parts to come after. This will allow you to reference any aliases produced at your inner SELECT in your GROUP ... ORDER ... parts.

However, since the GROUP moves to a different level, you'll have to move the aggregations like SUM to the new level as well. Also, you'll lose direct access to your tables, so mostly likely you'll have to change table aliases used in GROUP ... ORDER ... segments to T, plus making sure all columns the GROUP ... ORDER ... parts are using are produced in the inner SELECT. All this should be easy to handle though.

Upvotes: 0

Wes H
Wes H

Reputation: 4439

You can't group by the expression because it is an aggregate. The group by values are used to determine the level at which an aggregate operates.

As a simplied example:

Select Count(*), 
       Object_id
From sys.columns
Group by Object_id

This is fairly easy to understand. Count the number of column records for each object_id.

What you are trying to do would be the equivalent of 

SELECT      COUNT( * ),
            object_id
  FROM      sys.columns
  GROUP BY  COUNT( * ),
            object_id

For the count of each object_id, give me the count of each object_id. That doesn't make sense.

The error message you're getting:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

is telling you that you can't group by an aggregate expression.

Upvotes: 0

Shahrzad Jahanbaz
Shahrzad Jahanbaz

Reputation: 108

SQL is implemented as if a query was executed in the following order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in SQL Server, you cant use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

Upvotes: 3

Related Questions