Question Re: SUM multiple columns together in Technology Q&A

SUM multiple columns together

aalexis — Fri, 15 Mar 2024 21:17:26 GMT

Hi All,

I would like to sum all 16 period amounts using a SUM function in an ABAP CDS. The sum function works in SQL but not in ABAP CDS View.

SUM (WKG001 + WKG002 +...+ WKG016) as WKGAMT and I am getting an error. But the same kind of statement works in SQL. but not in ABAP CDS.

SQL Statement is like below is working fine in SQL Console

select OBJNR, WRTTP, VRGNG, BEKNZ,

sum( WKG001 + WKG002 + WKG003 + WKG004 + WKG005 + WKG006 + WKG007 + WKG008 ) as WKGAMT1,

sum( WKG009 + WKG010 + WKG011 + WKG012 + WKG013 + WKG014 + WKG015 + WKG016 ) as WKGAMT2

from COSP

where objnr = 'AB9999999999'

and wrttp = '04'

and vrgng = 'COIN'

and beknz = 'L'

group by

OBJNR, WRTTP, VRGNG, BEKNZ

CDS View is below

@VDM.viewType: #BASIC

define view ZB_COSPSUM

//select from cosp as A

//left outer join tka01 as B on B.kokrs = 'A000'

select from ZB_COSP

{

objnr,

wrttp,

vrgng,

beknz,

@Semantics.currencyCode: true

Currency,

@Semantics.amount.currencyCode: 'Currency'

@DefaultAggregation: #SUM

sum( wkg009 + wkg010 + wkg011 + wkg012 + wkg013 + wkg014 + wkg015 + wkg016) as WKGAMT2

}

group by objnr, wrttp, vrgng, beknz, Currency

Any idea?

Thank you,

Arthur.

Re: SUM multiple columns together

Edrilan_Berisha — Fri, 15 Mar 2024 20:58:57 GMT

Hi Arthur,

without sharing your whole CDS view it's really hard to really get what you try here. Are these WKG0XX all columns in your CDS view?

There is also already a good blog post explaining how the sum function works: https://community.sap.com/t5/technology-blogs-by-members/accumulative-sum-using-abap-cds-no-amdp-function-implementation/ba-p/13539571

you need a group by in the end.

Best,

Edrilan Berisha

SAP S/4HANA Cloud Financials Development

Re: SUM multiple columns together

aalexis — Fri, 15 Mar 2024 21:11:35 GMT

Please see the whol CDS view

Re: SUM multiple columns together

aalexis — Fri, 15 Mar 2024 21:11:37 GMT

Please see the whol CDS view @VDM.viewType: #BASIC define view ZB_COSPSUM as //select from cosp as A //left outer join tka01 as B on B.kokrs = 'A000' select from ZB_COSP { objnr, wrttp, vrgng, beknz, @Semantics.currencyCode: true Currency, @Semantics.amount.currencyCode: 'Currency' @DefaultAggregation: #SUM sum( wkg009 + wkg010 + wkg011 + wkg012 + wkg013 + wkg014 + wkg015 + wkg016) as WKGAMT2 } group by objnr, wrttp, vrgng, beknz, Currency

Re: SUM multiple columns together

aalexis — Fri, 15 Mar 2024 21:18:50 GMT

Updated my question with SQL and the CDS View

Re: SUM multiple columns together

Sandra_Rossi — Sun, 17 Mar 2024 08:31:06 GMT

Please use the buttons "..." and "</>" to make the code easy to read.

e.g.

select OBJNR, WRTTP, VRGNG, BEKNZ, sum( WKG001 + WKG002 + WKG003 + WKG004 + WKG005 + WKG006 + WKG007 + WKG008 ) as WKGAMT1, sum( WKG009 + WKG010 + WKG011 + WKG012 + WKG013 + WKG014 + WKG015 + WKG016 ) as WKGAMT2 from COSP where objnr = 'AB9999999999' and wrttp = '04' and vrgng = 'COIN' and beknz = 'L' group by OBJNR, WRTTP, VRGNG, BEKNZ

Re: SUM multiple columns together

aalexis — Fri, 23 Aug 2024 17:42:37 GMT

I broke sum into two steps (WKG001 - WKG004) and (WKG005 - WKG008) and sum the intermediate and it worked.