Aggregated field to use SUM/MIN based on a condition?

Question

I would like to know if it's possible to SUM if a field has a specific value otherwise take only the MIN (just one value of the aggregated records)

I tried something like that but it's syntactically not correct, so I'm looking for an alternative to achieve this:

SELECT FROM ZCDS
FIELDS ZCDS~networkID as networkID,
       ZCDS~assignment as assignment,
       CASE 
         WHEN ZCDS~assignment = 'N'
         THEN MIN( ZCDS~amount )
         ELSE SUM( ZCDS~amount )
       END as amount
GROUP BY ZCDS~networkID, ZCDS~assignment
INTO TABLE @DATA(result).

Maybe there is a way to determine if the SUM is already not 0, then use the case to stop adding more amount. But I don't know how to access the intermediate value of the SUM in a CASE, probably it's not possible as well

Expected input:

+----+-----------+------------+--------+
| ID | NetworkID | Assignment | Amount |
+----+-----------+------------+--------+
|  1 | D/01      | N          |      7 |
|  2 | D/01      | N          |      5 |
|  3 | D/01      | U          |     15 |
|  4 | D/01      | U          |     11 |
|  5 | D/02      | N          |     40 |
|  6 | D/02      | N          |     42 |
+----+-----------+------------+--------+

Expected output:

+-----------+------------+--------+
| NetworkID | Assignment | Amount |
+-----------+------------+--------+
| D/01      | N          |      5 |
| D/01      | U          |     26 |
| D/02      | N          |     40 |
+-----------+------------+--------+

score 1 · Answer 1 · answered Mar 05 '20 at 11:07

1

Of course there is a way. Use HAVING SUM(ZDS~amount) after GROUP BY.

SELECT FROM ZCDS
FIELDS ZCDS~ID as ID,
       ZCDS~assignment as assignment,
       CASE 
         WHEN ZCDS~assignment = 'N'
         THEN MIN( ZCDS~amount )
         ELSE SUM( ZCDS~amount )
       END as amount
GROUP BY ZCDS~ID, ZCDS~assignment
HAVING SUM( ZCDS~amount ) = 0
INTO TABLE @DATA(result).

answered Mar 05 '20 at 11:07

Jagger

10,350
9
51
93

My example has an intended syntax error, you can't use MIN and MAX inside a CASE (to my knowledge). What I want to know is if there is an alternative to achieve this – RaTiO Mar 10 '20 at 14:30
1

@Ratio, you can't use MIN/MAX inside a case but you can use CASE inside MIN like this `HAVING MIN( CASE m1~shkzg WHEN 'H' THEN 1 WHEN 'S' THEN -1 ELSE 0 END * m1~menge )`. It all depends on your requirement, without expected input/output I cannot advice smth more specific – Suncatcher Mar 24 '20 at 08:06

score 0 · Answer 2 · answered Apr 10 '20 at 14:32

As you put word CDS into question title then I propose you the CDS solution. I recreated your table structure and can confirm this code works on ABAP 7.50 at least

@AbapCatalog.sqlViewName: 'zsql_assign'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'assignment'
define view ZCDS_ASSIGN as select from zfilter {
    key networkid,
    key assignment,
    case assignment when 'U' then sum( amount )
                    else          min( amount )
    end as amount
} group by networkid, assignment;

The output of this query:

In classical ABAP (OpenSQL) this is still not possible.

Aggregated field to use SUM/MIN based on a condition?

2 Answers2

Linked