CODEWITHSUNDEEP
sqlms-access
rohrl77
rohrl77

Reputation: 3337

How to create distinct count from queries with several tables

I am trying to create one single query that will give me a distinct count for both the ActivityID and the CommentID. My query in MS Access looks like this:

SELECT 
    tbl_Category.Category, Count(tbl_Activity.ActivityID) AS CountOfActivityID, 
    Count(tbl_Comments.CommentID) AS CountOfCommentID
FROM tbl_Category LEFT JOIN 
    (tbl_Activity LEFT JOIN tbl_Comments ON 
        tbl_Activity.ActivityID = tbl_Comments.ActivityID) ON 
        tbl_Category.CategoryID = tbl_Activity.CategoryID
WHERE 
    (((tbl_Activity.UnitID)=5) AND ((tbl_Comments.PeriodID)=1))
GROUP BY 
    tbl_Category.Category;

I know the answer must somehow include SELECT DISTINCT but am not able to get it to work. Do I need to create multiple subqueries?

Upvotes: 0

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

This is really painful in MS Access. I think the following does what you want to do:

SELECT ac.Category, ac.num_activities, aco.num_comments
FROM (SELECT ca.category, COUNT(*) as num_activities
      FROM (SELECT DISTINCT c.Category, a.ActivityID
            FROM (tbl_Category as c INNER JOIN
                  tbl_Activity as a
                  ON c.CategoryID = a.CategoryID 
                 ) INNER JOIN
                 tbl_Comments as co
                 ON a.ActivityID = co.ActivityID
            WHERE a.UnitID = 5 AND co.PeriodID = 1
           ) as caa
      GROUP BY ca.category
     ) as ca LEFT JOIN
     (SELECT c.Category, COUNT(*) as num_comments
      FROM (SELECT DISTINCT c.Category, co.CommentId
            FROM (tbl_Category as c INNER JOIN
                  tbl_Activity as a
                  ON c.CategoryID = a.CategoryID
                 ) INNER JOIN
                 tbl_Comments as co
                 ON a.ActivityID = co.ActivityID
            WHERE a.UnitID = 5 AND co.PeriodID = 1
           ) as aco
      GROUP BY c.Category
     ) as aco
     ON aco.CommentId = ac.CommentId

Note that your LEFT JOINs are superfluous because the WHERE clause turns them into INNER JOINs. This adjusts the logic for that purpose. The filtering is also very tricky, because it uses both tables, requiring that both subqueries have both JOINs.

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

You can use DISTINCT:

SELECT 
    tbl_Category.Category, Count(DISTINCT tbl_Activity.ActivityID) AS CountOfActivityID, 
    Count(DISTINCT tbl_Comments.CommentID) AS CountOfCommentID
FROM tbl_Category LEFT JOIN 
    (tbl_Activity LEFT JOIN tbl_Comments ON 
        tbl_Activity.ActivityID = tbl_Comments.ActivityID) ON 
        tbl_Category.CategoryID = tbl_Activity.CategoryID
WHERE 
    (((tbl_Activity.UnitID)=5) AND ((tbl_Comments.PeriodID)=1))
GROUP BY 
    tbl_Category.Category;

Upvotes: 0

Related Questions