Q: Calculated field from another table in OpenSQL The below example works in MySql, how can I achive this in OpenSql?
 SELECT StudentID, Name,
     ( SELECT COUNT(*) FROM StudentExam
      WHERE StudentExam.StudentID = Student.StudentID )
      AS ExamsTaken
   FROM Student
   ORDER BY ExamsTaken DESC;

SAP_BASIS version: 740-13   

A: Why don't you just use INNER JOIN and GROUP BY? If you want to present also the students who have taken no exams then you can achieve it with UNION.
SELECT StudentID, Name, COUNT(*) AS ExamsTaken
  FROM Student INNER JOIN StudentExam
  ON Student.StudentID = StudentExam.StudentID
  GROUP BY StudentID, Name
UNION
SELECT StudentID, Name, 0 AS ExamsTaken
  FROM Student
  WHERE StudentID NOT IN (SELECT StudentID FROM StudentExam WHERE StudentID =
    Student.StudentID)
  ORDER BY ExamsTaken DESC.

And here's some real life example in OpenSQL.
REPORT zzz.

START-OF-SELECTION.
  SELECT scarr~carrid, scarr~carrname, COUNT(*) AS examstaken
    FROM scarr INNER JOIN sflight
    ON scarr~carrid = sflight~carrid
    GROUP BY scarr~carrid, scarr~carrname
  UNION
  SELECT scarr~carrid, scarr~carrname, 0 AS examstaken
    FROM scarr
    WHERE carrid NOT IN ( SELECT carrid FROM sflight WHERE carrid = scarr~carrid )
  ORDER BY examstaken DESCENDING " <== applies to the union of both SELECT
  INTO TABLE @DATA(g_tab_result).
BREAK-POINT.

Here is the result.

