×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Derived Column with Rank and Qualify

Derived Column with Rank and Qualify

Derived Column with Rank and Qualify

(OP)
I am using teradata.

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?

RE: Derived Column with Rank and Qualify

Try this :
SELECT    'Total      ' AS Row_One
    ,    Name
    ,    Column2_Sum
    ,    Column3_Sum
    ,    Column4_Sum
    ,    Total_Sum
    ,    RANK(Total_Sum) Total_Sum_Rank
FROM    
    (    SELECT    Name
            ,    SUM (Column2) AS Column2_Sum
            ,    SUM (Column3) AS Column3_Sum
            ,    SUM (Column4) AS Column4_Sum
            ,    (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum
        FROM [table]
        GROUP BY Name
    )
ORDER BY Total_Sum DESC
QUALIFY Total_Sum_Rank <= 10
;

RE: Derived Column with Rank and Qualify

(OP)
al1024
\
thanks a lot.  Your fix looks excellent.  I try to run it, but teradata gives me a 'expecting something like a UDFCALLNAME keyword error between the ) and ORDER.  So now I need to work that out.  Also, I changed the name of the first column from ROW_ONE to COLUMN_ONE and tried adding that column to the subquery.  

so it looks like this:

SELECT    'Total      ' AS Column_One
    ,    Name
    ,    Column2_Sum
    ,    Column3_Sum
    ,    Column4_Sum
    ,    Total_Sum
    ,    RANK(Total_Sum) Total_Sum_Rank
FROM    
    (    SELECT  'Total      ' AS Column_One  
            ,    Name
            ,    SUM (Column2) AS Column2_Sum
            ,    SUM (Column3) AS Column3_Sum
            ,    SUM (Column4) AS Column4_Sum
            ,    (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum
        FROM [table]
        GROUP BY Name
    )
ORDER BY Total_Sum DESC
QUALIFY Total_Sum_Rank <= 10
;

i'll tinker with it some more.

RE: Derived Column with Rank and Qualify

I'm confused...
I forgot to give a name to the derived table.
Only add "as XXX" between ")" and "ORDER" and it will be fixed !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close