Question

The following SQL query returns one row per surgeon. The granularity before GROUP BY is a surgeon-panel combination, in other words, for each surgeon, one panel they performed in. Surgeons can only be listed once per panel, but a log can have more than one panel.

SELECT staff.STAFF_NM_WID "surgeon", COUNT( orl.LOG_ID ) "Number of Logs" FROM OR_LOG orl INNER JOIN V_LOG_STAFF staff ON orl.LOG_ID = staff.LOG_ID INNER JOIN OR_LOG_ALL_PROCS procs ON orl.LOG_ID = procs.LOG_ID
AND procs.LINE = 1 --First Listed Procedure
WHERE staff.STAFF_TYPE_MAP_NM = 'Physician'
GROUP BY Staff.STAFF_NM_WID
Panels are identified by the column procs.ALL_PROCS_PANEL. Logs are identified by the column orl.LOG_ID.

Which of the following steps are necessary to count the number of logs a surgeon performed in, while avoiding counting logs twice when a surgeon performed in more than one panel?
A) Change "SELECT" to "SELECT DISTINCT"
B) Change "Count(orl.LOG_ID)" to " DISTINCT Count(orl.LOG_ID)"
C) Change "Count(orl.LOG_ID)" to "COUNT (DISTINCT orl.LOG_ID)"
D) None of the Above.

Answer 1

C) Change "Count(orl.LOG_ID)" to "COUNT (DISTINCT orl.LOG_ID)"

Explanation:

DISTINCT orl.LOG_ID returns only the unique LOG_ID and count() returns the total count of unique LOG_ID.