Search code examples
countabapopensql

COUNT returns ambiguous result for LEFT JOIN query with NULLs


Is possible to count the left join'ed records? I would like to see count return the actual records joined (including 0 if nothing is left joined).

A join like the following worked in other forms of SQL I worked with, but OpenSQL does not seem to support count([column]):

select header~key, count(item~key) 
from header left join item on header~key = item~header_key
group by header~key.

I know the following query works, but if there are no items joined with a header, count will still be 1 and not 0:

select header~key, count(*) from header left join item group by header~key.

For now I have resorted to using abap (loop and loop at with group addition) for counting when I needed that. In my current scenario I am dealing with transaction data, which is massive in comparison to my previous projects and an extra loop increases processing time considerably.


Solution

  • It seems that DISTINCT is mandatory, i.e. COUNT( DISTINCT col ).

    This works in 7.52.

    EDIT : I removed my first query after discussing with Suncatcher, and added the more detailed answer below.

    Of course DISTINCT does not count duplicate values, so you could count the concatenation (DISTINCT CONCAT( … )) of all columns of the primary key.

    Let me show you the contents of SCARR and SPFLI, and then the final result.

    Table SCARR :

    CARRID
    ------
    FJ
    JL
    LH
    NG
    

    Table SPFLI :

    CARRID  CONNID
    ------  ------
    JL      0407
    JL      0408
    LH      0400
    LH      0401
    LH      0402
    LH      2402
    

    Open SQL :

    SELECT scarr~carrid, 
           COUNT( DISTINCT CONCAT( spfli~carrid, spfli~connid ) ) AS count
      FROM scarr
           LEFT OUTER JOIN spfli ON scarr~carrid = spfli~carrid
      GROUP BY scarr~carrid
      WHERE scarr~carrid IN ('FJ','JL','LH','NG')
      INTO TABLE @DATA(itab).
    

    Result :

    CARRID  COUNT
    ------  ------
    FJ      0
    JL      2
    LH      4
    NG      0