Creating Derived Column with Qualify and Rank

UDA
Fan

Creating Derived Column with Qualify and Rank

I am using Teradata SQL Assistant 7.1.

This is what I want to do:

SELECT

'Total ' as Row_One
, Name
, Sum (Column2) Column2_Sum
, Sum (Column3) Column3_Sum
, Sum (Column4) Column4_Sum
, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum
, Rank (Total_Sum) Total_Sum_Rank

FROM [table]

GROUP BY Name

ORDER BY Total_Sum DESC

Qualify Total_Sum_Rank <= 10

i know this code won't work because you can't + in a select. how can i duplicate the logic?
4 REPLIES
Enthusiast

Re: Creating Derived Column with Qualify and Rank

You can't do an aggregation combined with an ordered analytical function on the same column in a single pass. First, do the aggregation to a derived table, then do the RANK function.

SELECT Row_One
, Name
, Column2_Sum
, Column3_Sum
, Column4_Sum
, Total_Sum
, Rank (Total_Sum) Total_Sum_Rank
FROM (
SELECT

'Total ' as Row_One
, Name
, Sum (Column2) Column2_Sum
, Sum (Column3) Column3_Sum
, Sum (Column4) Column4_Sum
, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum

FROM [table]

GROUP BY Name ) DT1

ORDER BY Total_Sum_Rank

Qualify Total_Sum_Rank <= 10
;

Senior Apprentice

Re: Creating Derived Column with Qualify and Rank

Hi Barry,
of course you can mix OLAP and aggregates within the same query, you just have to use ANSI-style:

SELECT

'Total ' as Row_One
, Name
, Sum (Column2) Column2_Sum
, Sum (Column3) Column3_Sum
, Sum (Column4) Column4_Sum
, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum
, Rank () OVER (ORDER BY Total_Sum DESC) Total_Sum_Rank
...

OLAP functions are processed *after* GROUP BY/HAVING...

Dieter
Enthusiast

Re: Creating Derived Column with Qualify and Rank

Hey Deiter,

I knew you could combine the two in the ANSI style, but was trying to keep it in the context of the Teradata style.

Thanks for adding that clarification though, because that would have also been a valid option.

Barry
Fan

Re: Creating Derived Column with Qualify and Rank

the following produced the desired results:

SELECT top 10

'Total ' as Row_One
, Name
, Sum (Column2) Column2_Sum
, Sum (Column3) Column3_Sum
, Sum (Column4) Column4_Sum
, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum

FROM [table]

GROUP BY Name

ORDER BY Total_Sum DESC

teradata does the top AFTER the order by, so it works out!