SUM multiple columns together

[aalexis]

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

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

Any idea?

Thank you,

Arthur.

[Sandra_Rossi]

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

[Edrilan_Berisha · Accepted solution]

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-fun...

you need a group by in the end.

Best,

Edrilan Berisha

SAP S/4HANA Cloud Financials Development

[aalexis · Accepted solution]

Please see the whol CDS view

[aalexis · Accepted solution]

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

[aalexis · Accepted solution]

Updated my question with SQL and the CDS View

[aalexis · Accepted solution]

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