Need help with Partition by to roll up values

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Fan

Need help with Partition by to roll up values

Hi All,

 

I'm new to Teradata and I have a requeriment to aggergate vlaues(roll up) at different levels.How to get the total sum of a column. Just the SUM function won't work. Appricate any help on this. Thanks in advance.

 

Raja

 

 

 

 

2 REPLIES
Junior Apprentice

Re: Need help with Partition by to roll up values

Hi Raja,

 

It might help if you could give an example of what you're trying to do.

 

In the meantime, check the ROLLUP functionality at http://info.teradata.com/htmlpubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1146_151K/ch01.021...

That might give you what you want.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Need help with Partition by to roll up values

Hi Dave,

Thanks for you reply. Here is my code.

SELECT*(       
 SELECT DISTINCT
            
                WORK_TYP,
                WORK_DT,
                OFFICE_NAM, 
                MANAGER_ID,
                EMP_ID,
                
                WORK_DUR_HR,
                 
                  SUM(WORK_DUR_HR) OVER (PARTITION BY WORK_DT) AS SumWORK_DUR_HR,
                   SUM(WORK_DUR_HR) OVER (PARTITION BY WORK_DT,WORK_TYP) AS SumWORK_DUR_HR_1,
                   SUM(WORK_DUR_HR)OVER (PARTITION BY  WORK_TYP,WORK_DT,LGNUM) AS SumWORK_DUR_HR_2,
                   SUM(WORK_DUR_HR)OVER (PARTITION BY  WORK_TYP,OFFICE_NAM,MANAGER_ID) AS SumWORK_DUR_HR_3,
                SUM(WORK_DUR_HR)OVER (PARTITION BY  WORK_TYP,OFFICE_NAM,EMP_ID,MANAGER_ID) AS SumWORK_DUR_HR_4,  
                SUM(WORK_DUR_HR) OVER (ORDER BY WORK_TYP) AS SumWORK_DUR_HR_5,
                
                NON_WORK_HR,               
                   SUM(NON_WORK_HR)  OVER (PARTITION BY WORK_DT) AS SumNON_WORK_HR_DT,
                   SUM(NON_WORK_HR) OVER (PARTITION BY WORK_DT,WORK_TYP) AS SumNON_WORK_HR_DT_1,
                   SUM(NON_WORK_HR)OVER (PARTITION BY  WORK_TYP,WORK_DT,LGNUM) AS SumNON_WORK_HR_DT_2,
                   SUM(NON_WORK_HR)OVER (PARTITION BY  WORK_TYP,LGNUM,MANAGER_ID) AS SumNON_WORK_HR_3,
                SUM(NON_WORK_HR)OVER (PARTITION BY  WORK_TYP,OFFICE_NAM,EMP_ID,MANAGER_ID) AS SumNON_WORK_HR_4, 
                SUM(NON_WORK_HR)  OVER (ORDER BY WORK_TYP) AS SumNON_WORK_HR_DT_5,
                                        
                WHOLE_HRS,
                SUM(WHOLE_HRS) OVER (PARTITION BY WORK_DT) AS SumWHOLE_HRS,
                   SUM(WHOLE_HRS) OVER (PARTITION BY WORK_DT,WORK_TYP) AS SumWHOLE_HRS_1,
                   SUM(WHOLE_HRS)OVER (PARTITION BY  WORK_TYP,WORK_DT,LGNUM) AS SumWHOLE_HRS_2,
                   SUM(WHOLE_HRS)OVER (PARTITION BY  WORK_TYP,LGNUM,MANAGER_ID) AS SumWHOLE_HRS_3,
                SUM(WHOLE_HRS)OVER (PARTITION BY  WORK_TYP,OFFICE_NAM,EMP_ID,MANAGER_ID) AS SumWHOLE_HRS_4,
                SUM(WHOLE_HRS) OVER (ORDER BY WORK_TYP) AS SumWHOLE_HRS_5,       
                FROM
                   TABLE1                             
         
              GROUP BY
              WORK_TYP,
              WORK_DT,
              OFFICE_NAM, 
              MANAGER_ID,
              EMP_ID,
              WORK_DUR_HR,
                 WHOLE_HRS
            
          )
                  
                  
                       WHERE
         WORK_DT>='2017-01-27'
          AND
    WORK_DT<='2017-01-31'
     AND
     OFFICE_NAM IN ('A1','B1').

 

I'm trying to get the overall total of WORK_DUR_HR, NON_WORK_HR AND WHOLE_HRS for the given date range and the office_nam for secific work_typ.For example, if there are work_typ a,b,c,d,e and f. We need to sum the WORK_DUR_HR for work_typ a,b,c,d  and sum WORK_DUR_HR for e and f. When I try to get sum using(ORDER BY WORK_TYP) It no giving me the correct sum. May be it is pulling all the data irrespective of the date and office.Need help with this issue. Thanks for all your help.

Raja