How to sum up the columns total value in the last row

General
Enthusiast

How to sum up the columns total value in the last row

Hi ,

I have folliwng query for summing u the value

Select LOB , CMPGN_NAME ,INC_TRXN_CD as TRANSACTION_CD  , sum ( INC_AMT ) as TOTAL_DOLLAR_AMT  , count (*) as TOTAL_ACCTS from UD466.FF_PRCSS where Elg_Flg='Y'

group by 1,2,3;

It gives result like below








LOB CMPGN_NAME TRANSACTION_CD TOTAL_DOLLAR_AMT TOTAL_ACCTS
CONS Fulfillment Exception Process 1281 300 2
CONS Associate Checking Remediation Risk Event (GRC 19480) 1232 82.31 7
CONS CCA Marketing Behavior Test - 001 1282 4700 26
CONS Fulfillment Exception Process 1180 400 2
CONS DD Cash Promo Code Standard Fulfillment Process 1180 8000 39
CONS Fulfillment Exception Process 1282 330 3
CONS CCA Marketing Behavior Test - 002 1282 17500 98

but i want sum(total dollar amount) and sum(total_accts) , is these any way to do this

17 REPLIES
Enthusiast

Re: How to sum up the columns total value in the last row

It seems formating issue in the original post .....Currently my result somewhat look like below

LOB         CMPG_NAME              TRANSACTION_CD           TOTAL_DOLLAR_AMT          TOTAL_ACCTS

CON         ABC                            1111                                     1000                                  10

XYZ            123                           2222                                     2000                                  5

But i want result like

LOB         CMPG_NAME              TRANSACTION_CD           TOTAL_DOLLAR_AMT          TOTAL_ACCTS

CON         ABC                            1111                                     1000                                  10

XYZ            123                           2222                                     2000                                  5

                                                                                   TOTAL :- 3000                 TOTALACCTS :- 15

Senior Apprentice

Re: How to sum up the columns total value in the last row

If this query is run in SQL Assistant you can simply press F4 in the answer set window to get a final row with sums for all numeric columns.

If this is a BTEQ script you could add a WITH SUM(TOTAL_DOLLAR_AMT), SUM(TOTAL_ACCTS) at the end of the query. I'm not shure if the syntax is correct, didn't use that for years.

Otherwise use GROUPING SETS which can return multiple aggregation levels within the same query:

Select LOB , CMPGN_NAME ,INC_TRXN_CD as TRANSACTION_CD  , sum ( INC_AMT ) as TOTAL_DOLLAR_AMT  , count (*) as TOTAL_ACCTS from UD466.FF_PRCSS where Elg_Flg='Y'
group by grouping sets ((1,2,3), ())

To sort your result you should use GROUPING like

order by grouping(LOB), LOB, grouping(CMPGN_NAME), CMPGN_NAME, grouping(INC_TRXN_CD), INC_TRXN_CD

Dieter

Enthusiast

Re: How to sum up the columns total value in the last row

Thanks Dieter above query gives me the answer but i dont want represent first three columns as NULL and the total of other columns

it looks like table values rather summation of total sum.

For display purpose do you know what is the exact syntex of WITH SUM(TOTAL_DOLLAR_AMT), SUM(TOTAL_ACCTS)

its giving me error " cannot nest aggeragate opertions"

Enthusiast

Re: How to sum up the columns total value in the last row

Would be great if it show TOTAL in column C ( TRANSACTION_CD ) and the TOTAL of DOLLAR amount and total of account numbers

like below

LOB         CMPG_NAME              TRANSACTION_CD           TOTAL_DOLLAR_AMT          TOTAL_ACCTS

CON         ABC                            1111                                     1000                                  10

XYZ            123                           2222                                     2000                                  5

                                                  TOTAL                                    3000                                 15

Is it possible?

Senior Apprentice

Re: How to sum up the columns total value in the last row

For BTEQ don't use the column alias but the original name, i told you i didn't use it for years :-)

WITH sum ( INC_AMT )  , count (*)

And for GROUPING SETS you get rid of the NULLs using GROUPING and CASE (but then you can't use the ordinal position within GROUP BY, must use the column name instead) like

case when grouping(LOB) = 1 then 'TOTALS' else LOB end  

Dieter

Enthusiast

Re: How to sum up the columns total value in the last row

Select LOB,

CMPGN_NAME,

INC_TRXN_CD as TRANSACTION_CD ,

sum ( INC_AMT ) as TOTAL_DOLLAR_AMT , count (*) as TOTAL_ACCTS

from UD466.FF_PRCSS where Elg_Flg='Y'group by grouping sets ((1,2,3), ())

order by grouping(LOB), LOB, grouping(CMPGN_NAME), CMPGN_NAME, grouping(INC_TRXN_CD), INC_TRXN_CD

Some how i am not able to write the query to ignore NULL and use TOTAL in third , can you tell me the exact syntex . please?

Senior Apprentice

Re: How to sum up the columns total value in the last row

Select case when grouping(LOB) = 1 then '' else LOB end,
case when grouping(CMPGN_NAME) = 1 then '' else CMPGN_NAME end,
case when grouping(INC_TRXN_CD) = 1 then 'TOTAL' else INC_TRXN_CD end,
sum ( INC_AMT ) as TOTAL_DOLLAR_AMT , count (*) as TOTAL_ACCTS
from UD466.FF_PRCSS where Elg_Flg='Y'
group by grouping sets ((LOB,CMPGN_NAME,INC_TRXN_CD), ())
order by grouping(LOB), LOB, grouping(CMPGN_NAME), CMPGN_NAME, grouping(INC_TRXN_CD), INC_TRXN_CD

Dieter

Enthusiast

Re: How to sum up the columns total value in the last row

Thank you so much Dieter :) This is great :)

Enthusiast

Re: How to sum up the columns total value in the last row

Hello Folks,  

I have a requirement and should bring d data in below format

(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total

A.        Xx.     Vvv.          532                                                                             532

B.      Cc          Cv.                                           453                                            453

c.       Ft.         As.                                                                     543.                543           

From d above example the date1 and date 2 are date Columns and it's being compared and difference is found as number of days . i.e. if date1>date 2 then date1-date2 else date2-date1 which would giv d number of days difference.

 

Now based on this number of days difference calculation the SUM is calculated group by on column 1 and will come under the respective days bucket, either (Sum for days1-10.)or (Sum days11-20) or  (sumdays21-30).

 

And the last column is the SUM of that specific row.

 

Hope I made my requirement clear, now how do I handle this at the Teradata query Level, Please help.

Thanks in Advance.

Best Regards,

Shavyani :)