Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 07 Nov 2007 @ 19:32:42 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Calculate Percentages for a column
 
From:   Dieter Noeth

Oscar Valles wrote:

          > SELECT A1.OFFICE, SUM(A1.HOURS) AS HOURS, (SUM(A1.HOURS) / (SELECT
          > SUM(A2.HOURS) FROM TABLE AS A2)) AS HRSPCT
          > >FROM TABLE AS A1
          > WHERE A1.OFFICE LIKE 'AD%'
          > GROUP BY A1.OFFICE;
  When I execute this query it tells me that I am missing something by the '(' and the 'SELECT' statement. I am fairly new to Teradata and SQL so any response is greatly appreciated.  


Teradata SQL stil not supports Scalar Subqueries (= Select within the column list), you usually have to rewrite it using Outer Joins or OLAP functions:

In your case it's:

     SELECT A1.OFFICE, SUM(A1.HOURS) AS HOURS,
     SUM(A1.HOURS) / sumHours AS HRSPCT
     FROM TABLE AS A1,
     (SELECT SUM(A2.HOURS) as sumHours FROM TABLE AS A2)
     WHERE A1.OFFICE LIKE 'AD%'
     GROUP BY A1.OFFICE;

or

     SELECT A1.OFFICE, SUM(A1.HOURS) AS HOURS,
     SUM(A1.HOURS) / (SUM(SUM(A1.HOURS)) over ()) AS HRSPCT
     FROM TABLE AS A1
     GROUP BY A1.OFFICE
     qualify A1.OFFICE LIKE 'AD%'
     ;

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023