Ranking in Teradata- SQL

I am trying to rank my sales data using the rank() over function . Here is my code :

Select
Category as CAT
,units*cost as COST_SALES
,units*retail as RETAIL_COST
,units as UNITS_SOLD
,RANK() OVER (PARTITION BY 1 ORDER BY 3 DESC ) AS RANKING
from Table
Where date between current_date-7 and current_date
group by 1 

When I get my result it is unordered and shows rank 1 for all the categories.

1 Answer

You can't use column references in the window functions. You need to name the columns explicitly:

Select Category as CAT, units*cost as COST_SALES, units*retail as RETAIL_COST, units as UNITS_SOLD, RANK() OVER (PARTITION BY Categroy ORDER BY units*retail DESC ) AS RANKING
from Table
Where date between current_date-7 and current_date
group by Category;