COUNT returns ambiguous result for LEFT JOIN query with NULLs

Question

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.

Answer 1

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