All Forums General
Niesh20us 78 posts Joined 06/13
05 Jul 2013
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

Niesh20us 78 posts Joined 06/13
05 Jul 2013

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
   
 

dnoeth 4628 posts Joined 11/04
05 Jul 2013

 
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

Dieter

Niesh20us 78 posts Joined 06/13
05 Jul 2013

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"

Niesh20us 78 posts Joined 06/13
05 Jul 2013

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?
 

dnoeth 4628 posts Joined 11/04
05 Jul 2013

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

Dieter

Niesh20us 78 posts Joined 06/13
05 Jul 2013

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?

dnoeth 4628 posts Joined 11/04
05 Jul 2013
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

Dieter

Niesh20us 78 posts Joined 06/13
05 Jul 2013

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

shavyani 23 posts Joined 03/15
05 Apr 2016

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 :)

dnoeth 4628 posts Joined 11/04
05 Apr 2016

Hi Shavyani,
if I understand you correctly it's a conditional aggregate like:

select
   colA
  ,sum(case when abs(date2-date1) between  1 and 10 then col end)
  ,sum(case when abs(date2-date1) between 11 and 20 then col end)
...
group by 1

 

Dieter

shavyani 23 posts Joined 03/15
06 Apr 2016

Hello Dieter,
Yes it's correct, but don't we have to use partition by here or a simple case as above would work?
Please Advise.
Thanks in Advance :)
Thanks Regards,
Shavyani.

dnoeth 4628 posts Joined 11/04
06 Apr 2016

Hi Shavyani,
I don't know exactly what you want as you didn't show source data & expected result.
If you need to return both the sum and the date details you might need to add OVER (PARTITION BY whatever). Or CASE without any aggregate is what you want...

Dieter

shavyani 23 posts Joined 03/15
07 Apr 2016

Hello Dieter ,
Thank You, yes I would like to view Cust, date columns and pull the other 7-8 dimension columns from my source table. But the Sum calculated should do the aggregation only on CUST and not on other columns like date and other dimension columns.
Please Advice.
Thanks in Advance.
Regards,
Shavyani

shavyani 23 posts Joined 03/15
14 Apr 2016

Hi All ,
In the same example as above , I will have to calculate the column wise "SUM" as mentioned in 'Bold" in the example below.
(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total
SUM                                533                          458                    543                1543
A.        Xx.     Vvv.          532                                                                             532
B.      Cc          Cv.                                           453                                            453
c.       Ft.         As.                                                                     543                  543   
D       hh          xx              1                                                                              1        
E        ff           mm                                             5                                             5         
I want this SUM to be as the first row in the entire data set , 
I think of something like below that's in bold.... will this work? or is there any other function or workaround that I could use to calculate this , Please help 
SELECT
CAST(NULL AS VARCHAR(20)) AS CUST,
CAST(NULL AS DATE FORMAT 'DD-MMM'YYYY') AS DT1,
CAST(NULL AS DATE FORMAT 'DD-MMM'YYYY') AS DT2,
SUM(SUM_1TO10) , SUM(SUM_11TO20),SUM(SUM_21TO30),SUM(SUM_TOT ) 
UNION
SEL 
CUST, DT1, DT2,
CASE   WHEN DIFF >=1 AND DIFF <=10 THEN DIFF ELSE 0 END AS SUM_1TO10,
CASE   WHEN DIFF >=11 AND DIFF <=20 THEN DIFF ELSE 0 END AS SUM_11TO20,
CASE   WHEN DIFF >=21 AND DIFF <=30 THEN DIFF ELSE 0 END AS SUM_21TO30,
CASE   WHEN DIFF <1 OR  DIFF >30 THEN DIFF ELSE 0 END AS SUM_DEF,
SUM_1TO10+SUM_11TO20+SUM_21TO30+SUM_DEF AS SUM_TOT FROM 
(SEL   CUST, DT1, DT2,
SUM(
CASE 
WHEN DT1 > DT2 THEN DT1-DT2
WHEN DT2 > DT1 THEN DT2-DT1
ELSE 0
END) OVER (PARTITION BY CUST ORDER BY 1 ) AS DIFF
FROM 
(
SEL * FROM   (SEL 'A' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-11' AS DT2) A
UNION ALL
SEL * FROM   (SEL 'B' AS CUST, DATE '2001-01-21' AS DT1, DATE '2001-01-01' AS DT2) B
UNION ALL
SEL * FROM   (SEL   'C' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-31' AS DT2) C
UNION ALL
SEL * FROM   (SEL 'A' AS CUST, DATE '2001-02-21' AS DT1, DATE '2001-01-11' AS DT2) A
) A
) A
Regards,
Shavyani

dnoeth 4628 posts Joined 11/04
14 Apr 2016

You can do it using a seperate SELECT, but you should use "UNION ALL". Of course, if you already do any aggregation it will be more efficient to use "GROUPING SETS"
Why do you specify " (PARTITION BY CUST ORDER BY 1 )", this is useless?

Dieter

shavyani 23 posts Joined 03/15
14 Apr 2016

Hello dieter,
yea I'm trying to modify the code using grouping sets. I can use partition by "column name " but the problem is that the other columns in select clause (apart from the aggregate columns) are dynamic and would keep changing and even more than one column if a user wants to see, the number of columns would keep varying and all those columns should go into " Group By" or partition by.
your suggestions please.
Thank You.
Best Regards,
Shavyani

shavyani 23 posts Joined 03/15
15 Apr 2016

Hello,
Can anyone help me how would I bring this "Column Wise" sum as below highlighted in bold : using GROUPING SETs via Case Staments (If required)in the Query....Kindly refer to my previous post for the query :)
(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total
SUM                                533                          458                    543                1543
A.        Xx.     Vvv.          532                                                                             532
B.      Cc          Cv.                                           453                                            453
c.       Ft.         As.                                                                     543                  543   
D       hh          xx              1                                                                              1        
E        ff           mm                                             5                                             5  
Thanks a bunch in Advance :)
Best Regards,
Shavya    

You must sign in to leave a comment.