Issues with Counting Records with SQL across multiple tables-CodePudding

There are three tables: Cases, Calls, and SubEvents.

The table schema is a Case can have multiple Calls, and each Call can have multiple SubEvents. I'd like to use a query to get a count of all calls and a count of all sub-events (if there are any) for each case after a certain date.

For example, a case named John has 3 calls... the first call has 2 sub-events, the second call has 1 sub-event, and the third call has zero. So the query should return this result:

Case	Call Total	SubEvent Total
John	3	3

I've tried writing the query multiple ways, with subqueries etc, but I can't get it to work properly. The closest I've come is the query below, but this provides the incorrect count for Calls. It gives me 4 when it should give me 3. Another example I tried had 4 calls with 10 sub-events, but the query returned 11 total calls instead of 4.

I'd appreciate any help. My SQL has gotten rusty after a period of disuse, and this is in someone's Access database, which is pretty fickle when it comes to writing SQL queries.

SELECT c.casename, Count(e.callid) AS EventTotal, Count(s.id) AS SubEventTotal
FROM (cases AS c INNER JOIN calls AS e ON c.contactid = e.contactid) left JOIN tblSubEvents AS s ON e.callid = s.callid
WHERE e.calldate > #1/1/2022#
GROUP BY c.casename

CodePudding user response：

Consider joining two aggregate derived tables:

SELECT case.casename, c_agg.EventTotal, e_agg.SubEventTotal
FROM (case
INNER JOIN (
    SELECT contactid, COUNT(callid) AS EventTotal
    FROM calls
    WHERE calldate > CDate('2022-01-01')
    GROUP BY contactid
) c_agg
  ON case.contactid = c_agg.contactid)
LEFT JOIN (
    SELECT callid, COUNT(id) AS SubEventTotal
    FROM tblSubEvents
    GROUP BY callid
) e_agg
  ON c_agg.callid = e_agg.callid

CodePudding user response：

Try this:

SELECT
   c.casename
   ,Count(e.callid) AS EventTotal
   ,sum(s.id) AS SubEventTotal
FROM (cases AS c INNER JOIN calls AS e ON c.contactid = e.contactid)
left JOIN (select callid, count(s.id) as id from tblSubEvents s group by callid) AS s ON e.callid = s.callid
WHERE e.calldate > #1/1/2022#
GROUP BY c.casename

Basically, pushing the responsibility for the sub-event count to the join query.