11

I have to create a row_number column ordered by a grouped sum, when using sql:

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by billsum DESC) as rn
from Sales group by Sales.Name

It reports error because row_number over cannot parse the "billsum" alias, I have to write:

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by SUM(Sales.Bill) DESC) as rn
from Sales group by Sales.Name

so here I write SUM(Sales.Bill) twice, is there anyway to use the alias here?

demaxSH
  • 1,743
  • 2
  • 20
  • 27
  • 2
    Only by defining the alias in another part of the query such as a [CTE or APPLY](http://stackoverflow.com/questions/7269195/is-it-possible-to-reuse-aggregate-functions-results-in-the-same-select/7269212#7269212) part. Celko explains why [here](http://joecelkothesqlapprentice.blogspot.com/2006/06/reference-alias-field-name.html) – Martin Smith Sep 04 '11 at 05:48

4 Answers4

11

The MSDN docs for the T-SQL OVER clause say:

value_expression cannot refer to expressions or aliases in the select list.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
2

As already stated out by other member you either have to use CTE or SubQuery.

Not only Row_Number() function but in tsql you can not reference alias in same query, so either you have to use one of the mentioned way above or the expression you used in your post. I hope it makes sense!! :)

Nilesh Thakkar
  • 2,877
  • 1
  • 24
  • 43
1

Possible work-arounds are to use CTE or a subquery:

SELECT Name, billsum, ROW_NUMBER() OVER (ORDER BY billsum DESC) AS rn
FROM 
  ( SELECT Sales.Name, SUM(Sales.Bill) AS billsum 
    FROM Sales
    GROUP BY Sales.Name
  ) tmp
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
-1
-- Reorder after cutting out qty = 0.
SELECT  *,ROW_NUMBER()  OVER  (partition by claimno ORDER BY itemno) as 'alias name'
from  dbo.OrderCol
where QTY <> 0
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Yeen
  • 1