CDS View SQL Aggregate breakdown

Question

I wanted to ask how can i see which values have been aggregated during the aggregation when using for example a sum agg in SQL.

E.G

Product	Quan
A	5
A	10

Consider this i want to agg the Quan and to breakdown the values used. Result should look lke this:

Product	Agg_Quan	Details
A	15	5, 10

Currently i have this abap class executing some select query. I want to find a way either by directly executing a cds view or with an abap class.

CLASS zcl_flights_demo_cds IMPLEMENTATION.
METHOD flights_connections
    BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ZWODPROBLEM.

        itab_cities =
            SELECT
                ZWODPROBLEM.mandt as client,
                ZWODPROBLEM.product as airline_code,
                CAST(ZWODPROBLEM.phyquan AS INT) as total_phyquan,
                ZWODPROBLEM.Origin2 as city_to,
                SUM(ZWODPROBLEM.phyquan) OVER (PARTITION BY ZWODPROBLEM.product) as total_phyquan_agg
            FROM ZWODPROBLEM;

       RETURN
            SELECT client,
                    airline_code,
                    total_phyquan_agg as total_phyquan,
                    STRING_AGG(CONCAT(city_to, CONCAT('-', total_phyquan)), ', ') as cities_to
             FROM :itab_cities
             GROUP BY client, airline_code, total_phyquan_agg, city_to, total_phyquan;
ENDMETHOD.

ENDCLASS.

Thanks!

Answer 1

Ah i solved it.

> METHOD flights_connections
>         BY DATABASE FUNCTION
>         FOR HDB
>         LANGUAGE SQLSCRIPT
>         OPTIONS READ-ONLY
>         USING ZWODPROBLEM.
> 
>             itab_coo =
>                 SELECT
>                     ZWODPROBLEM.mandt as client,
>                     ZWODPROBLEM.docNr as docNr,
>                     ZWODPROBLEM.Country_of_Origin as restriction,
>                     cast(ZWODPROBLEM.quantity as INT) as ODQuantity,
>                     ZWODPROBLEM.product as airline_code,
>                     sum(ZWODPROBLEM.phyquan) as total_phyquan,
>                     STRING_AGG(concat(ZWODPROBLEM.coo_item, concat('-',cast(ZWODPROBLEM.phyquan as INT))), ', ') as city_to
>                 FROM ZWODPROBLEM
>                 GROUP BY ZWODPROBLEM.mandt,ZWODPROBLEM.docNr,ZWODPROBLEM.Country_of_Origin,ZWODPROBLEM.quantity,ZWODPROBLEM.product;
>            RETURN
>                 SELECT client,
>                         docNr,
>                         restriction,
>                         ODQuantity,
>                         airline_code,
>                         total_phyquan as total_phyquan,
>                         city_to as cities_to
>                  FROM :itab_coo
>                  GROUP BY client,airline_code,restriction, ODQuantity, docNr,total_phyquan,city_to;
>     ENDMETHOD.

Now i get the aggregated values separated by its origin in one column:

Total Quantity ---- Summed

200 ---- -50, TR-2, TR-2, TR-3, TR-3, TR-85, IT-10, AT-10, -23, -2, -10