Exam 70-761 topic 1 question 79 discussion

Answer B is wrong because the COUNT(*) returns 1, it should be COUNT(U.UserId)

Actually, none of the answers works... (I checked). D answer needs a join that doesn't have B answer should be COUNT(U.UserID) otherwise COUNT(*) even without active users always returns the value 1

None of the options are correct: The correct answer is option A from question 43. select R.RoleID, count (u.UserID) by #tblRole R LEFT JOIN ( SELECT j.UserID, j.RoleID FROM #tblUsers j where j.IsActive = 1 ) u in r.RoleID = u.RoleID GROUP BY r.RoleID What the subquery does is take only the users that are active, that is, only the IsActive = 1 but as in the outside query they are compared to be the same and since it is a LEFT JOIN, obviously those with IsActive = 0 will be then since it is a LEFT JOIN, I know if they are going to show but they are NULL, the count () sends it to 0, COUNT (*) should not be used because if it returns 1 since COUNT (*) even counts the nulls.

This question is the same with Q43 in these questions and Answer is: SELECT R.RoleName, COUNT(UserId) AS ActiveUserCount FROM tblRoles R LEFT JOIN (SELECT UserID, RoleID FROM tblUsers WHERE IsActive = 1) U ON U.RoleId = R.RoleId GROUP BY R.RoleId, R.RoleName (TESTED)

I think is just cropped a part of answer D, and it should be correct

I agree with Scooter123: SELECT R.RoleName, COUNT(UserId) AS ActiveUserCount FROM tb1Roles R LEFT JOIN (SELECT UserID, RoleID FROM tb1Users WHERE IsActive = 1) U ON U.RoleId = R.RoleId GROUP BY R.RoleId, R.RoleName I have simulated this as well

Hallo, I think the correct answer should be D, because there is a condition in the question "If a role has no active users, you must disply a zero as the active users count". To satisfy this condition the correct answer should be query D, where ISNULL (U.activeUseCount,0) is used to satify the above condition. https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017