Mysql – Case overcounting what is expected

casecountMySQLquerytroubleshooting

So I am attempting to setup a query that can track actions that are automated within our web system.
The first thing I am attempting to check is the reminders we are sending out to customers.

I have got a query that should be returning what I am expecting but it's overcounting and I have no idea what it's doing. The query is as follows (I'm sure its terribly written this isn't normally job):

    SELECT  COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-05-13%'
                      THEN O.`Order ID` END
                 ) AS 'Expected 35 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-35-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 35 day Emails Sent',
            COUNT(DISTINCT CASE WHEN O.`Application date` LIKE '2020-06-04%'
                  AND  E.type IS NULL THEN O.`Order ID` END
                 ) AS 'Expected 14 day Emails sent',
            COUNT(CASE WHEN C.`view` LIKE '%evidence-reminder-14-days'
                  AND  C.created_at LIKE '2020-06-19%' THEN C.`view` END
                 ) AS 'Actual 14 day Emails Sent'
        FROM  
        (
            SELECT  `permit_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', type AS 'Order type'
                FROM  `user_permit`
                WHERE  `status` = 'Awaiting Approval'
                UNION 
             SELECT  `voucher_id` AS 'Order ID', `created_at` AS 'Application Date',
                    `user_id`, `status` AS 'Order Status', `type` AS 'Order type'
                FROM  `user_voucher`
                WHERE  `status` = 'Awaiting Approval' 
        ) AS O
        LEFT JOIN  
        (
            SELECT  `view`, ref, receiver_id AS 'user_id', created_at
                FROM  communication
                WHERE  `view` LIKE '%evidence%') AS C  ON (O.`Order ID` = C.`ref`
        )
        LEFT JOIN  user_evidence E  ON (O.user_id = E.user_id);

Now the results I am given are 2, 16, 14, and 26. Having been through the communication table and Counted the entries I'm looking for I got 3 and 11 for the second and fourth result (I don't actually know how many results there should've been for the first and third results)

One of the main problems with the way that the communication table is setup is that as the orders may receive many reminders / other emails there are many entries with the same ref (Order ID) associated with them which many of the other examples of queries I've found online have in common.

The communication table has the format:

+--+----+---+------------------+-----------+----+----------+----------+
|ID|type|ref|communication_type|receiver_id|view|created_at|updated_at|

the view is the email template that is going out and is what is easiest to check.

If any more information is needed please ask.

Best Answer

COUNT before JOINing, else you will be counting the result of the JOIN.

Possibly it is as simple as putting the C counts in the C subquery; ditto for O. At that point, the subqueries may as well deliver only counts, not all the other columns. And, then, why JOIN?