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') 
))

score 0 · Answer 1 · answered Sep 18 '20 at 16:47

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')

The top seemed to work, but it is giving me a percent that is off. So for 100%, Im getting 0.1, for 92, Im getting 9.247843002942 (keeps going), does not follow the no decimal rule you have in your code. Any ideas? Thank you so much! — Alison Blazey, Sep 21 '20 at 12:28
When I add a *100 to fix this, it only works for the 0.1, any other number does not updated — Alison Blazey, Sep 21 '20 at 12:30

score 0 · Answer 2 · answered Sep 18 '20 at 16:50

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.

This did not work, not sure if it is because I have other queries as well. Its pulling student information above. I added "()" to fix that but still did not pull a result. Thank you for trying! — Alison Blazey, Sep 21 '20 at 12:15

How do I divide by two count statements? Getting error

2 Answers2