How do I divide by two count statements? Getting error

Question

I am trying to get Total # of Presents divided by Total # of Days. The Count statements work separately, but when I try to divide the two counts, the report does not run. Any ideas?

((SELECT COUNT(*)
FROM ATTENDANCE
    LEFT OUTER JOIN ATTENDANCE_CODE
        ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
    LEFT OUTER JOIN PERIOD
        ON PERIOD.ID = ATTENDANCE.PERIODID
WHERE ATTENDANCE.STUDENTID = STUDENTS.ID
AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID
AND ATTENDANCE_CODE.ATT_CODE = 'LZP'
)/
(SELECT COUNT(*)
FROM ATTENDANCE
    LEFT OUTER JOIN ATTENDANCE_CODE
        ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
    LEFT OUTER JOIN PERIOD
        ON PERIOD.ID = ATTENDANCE.PERIODID
WHERE ATTENDANCE.STUDENTID = STUDENTS.ID
AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID
AND (ATTENDANCE_CODE.ATT_CODE = 'A' or ATTENDANCE_CODE.ATT_CODE = 'LZA' or ATTENDANCE_CODE.ATT_CODE = 
'LZP') 
))

Answer 1

You need to select the result of that division. It is also a good idea to use NULLIF() in the denominator to avoid a potential division by zero error.

SELECT
    (SELECT COUNT(*)
    FROM ATTENDANCE
        LEFT OUTER JOIN ATTENDANCE_CODE
            ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
        LEFT OUTER JOIN PERIOD
            ON PERIOD.ID = ATTENDANCE.PERIODID
    WHERE ATTENDANCE.STUDENTID = STUDENTS.ID
    AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
    AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
    AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
    AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID
    AND ATTENDANCE_CODE.ATT_CODE = 'LZP'
    )/
    NULLIF((SELECT COUNT(*)
    FROM ATTENDANCE
        LEFT OUTER JOIN ATTENDANCE_CODE
            ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
        LEFT OUTER JOIN PERIOD
            ON PERIOD.ID = ATTENDANCE.PERIODID
    WHERE ATTENDANCE.STUDENTID = STUDENTS.ID
    AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
    AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
    AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
    AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID
    AND (ATTENDANCE_CODE.ATT_CODE = 'A' or ATTENDANCE_CODE.ATT_CODE = 'LZA' or ATTENDANCE_CODE.ATT_CODE = 
    'LZP') 
    ), 0)

Looking at the two subqueries, that are very similar, I wonder whether the whole thing could be simplified as :

SELECT AVG(ATTENDANCE_CODE.ATT_CODE = 'LZP')
FROM ATTENDANCE
LEFT OUTER JOIN ATTENDANCE_CODE
    ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
LEFT OUTER JOIN PERIOD
    ON PERIOD.ID = ATTENDANCE.PERIODID
WHERE 
    ATTENDANCE.STUDENTID = STUDENTS.ID
    AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
    AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
    AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
    AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID
    AND ATTENDANCE_CODE.ATT_CODE IN ('LZP', 'LZA', 'A')

Answer 2

Instead of two SELECT queries, use one query, sum the different conditions, and divide them.

SELECT SUM(ATTENDANCE_CODE.ATT_CODE = 'LZP') / SUM(ATTENDANCE_CODE.ATT_CODE IN ('A', 'LZA', 'LZP') AS attendance_ratio
FROM ATTENDANCE
INNER JOIN ATTENDANCE_CODE
    ON ATTENDANCE_CODE.ID = ATTENDANCE.ATTENDANCE_CODEID
INNER JOIN PERIOD
    ON PERIOD.ID = ATTENDANCE.PERIODID
WHERE ATTENDANCE.STUDENTID = STUDENTS.ID
AND ATTENDANCE.ATT_DATE BETWEEN '%param1%' AND '%param2%'
AND PERIOD.PERIOD_NUMBER BETWEEN '1' AND '8'
AND ATTENDANCE.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND ATTENDANCE.SCHOOLID = STUDENTS.SCHOOLID

Also, it doesn't look like you should be using LEFT OUTER JOIN here. ATTENDANCE_CODEID and PERIODID appear to be foreign keys, they should always find a match in the parent table.