UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Highlighted
##
##### Creating Derived Column with Qualify and Rank

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-26-2007
09:18 AM

04-26-2007
09:18 AM

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?

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-26-2007
12:55 PM

04-26-2007
12:55 PM

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

;

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

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2007
11:22 AM

05-03-2007
11:22 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2007
12:54 PM

05-03-2007
12:54 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2007
12:58 PM

05-03-2007
12:58 PM

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!

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!