Sum of sub query in cds view

Question

I am unable to sum of data in cds views. Earlier in Hana calculation view the below query give me result as per my desire

Select t1.ID, t1.Name, t1.qty, t2.amount from T1 Inner join (select ID, Name, Sum (amount) from t2 group by ID, Name) as T2

But sum of amount is not correct in cds.. pls help

Answer 1

You can use an aggregate expression in a SELECT statement. That way you can call an aggregate function from multiple rows on a results set:

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
select from snwd_so
{ key buyer_guid,
   @Semantics.currencyCode
    currency_code,
   @Semantics.amount.currencyCode: 'currency_code'
    sum(gross_amount) as sum_gross_amount }
group by buyer_guid, currency_code

So in your view, you could do something like:

sum(name_of_field) as sum_of_field