Performing total of a column in a temporary column in SAP

In SAP, when you need to calculate the total of a column and display it alongside individual records, you can use a subquery approach with SUM function and GROUP BY clauses. This technique allows you to perform aggregation in a temporary column while maintaining the detail-level data.

Using Nested SELECT with SUM Function

To perform the total of a column in a temporary column, you need to perform a SELECT operation on your inner query result. The inner query groups the data, while the outer query calculates the total sum.

Example

Here is a sample query that demonstrates this approach ?

SELECT DATE, FUND_ID, PPT_ID, SOURCE_ID, AMOUNT, SUM(AMOUNT) as TOTAL
FROM (
    SELECT
        AD.CHV_DATE.DATE,
        AD.CHV_FUND.FUND_ID,
        AD.CHV_PARTICT.PPT_ID,
        AD.CHV_SOURCE.SOURCE_ID,
        SUM(AD.CHV_PARTICT.AMOUNT) as AMOUNT
    FROM
        AD.CHV_DATE,
        AD.CHV_FUND,
        AD.CHV_PARTICT,
        AD.CHV_SOURCE
    WHERE
        AD.CHV_SOURCE.FUND_ID = AD.CHV_FUND.FUND_ID
        AND AD.CHV_SOURCE.DATE = AD.CHV_DATE.DATE
        AND AD.CHV_PARTICT.PPT_ID = AD.CHV_SOURCE.PPT_ID
        AND AD.CHV_DATE.DATE IN ('2017-08-02')
        AND AD.CHV_PARTICT.PPT_ID IN ('PPT0449')
    GROUP BY
        AD.CHV_DATE.DATE,
        AD.CHV_FUND.FUND_ID,
        AD.CHV_PARTICT.PPT_ID,
        AD.CHV_SOURCE.SOURCE_ID
)
GROUP BY DATE, FUND_ID, PPT_ID, SOURCE_ID, AMOUNT

Query Structure Explanation

The query consists of two parts:

Inner Query: Retrieves and groups data from multiple tables (CHV_DATE, CHV_FUND, CHV_PARTICT, CHV_SOURCE) with proper join conditions and filters for specific date and participant ID.

Outer Query: Takes the result from the inner query and calculates the total sum of amounts using SUM(AMOUNT) as TOTAL, creating a temporary column that shows the aggregate value alongside individual records.

Conclusion

This nested SELECT approach with SUM aggregation allows you to display both detailed records and their total sum in a single result set, providing comprehensive data analysis capabilities in SAP queries.