Count (Distinct) with left join and group by
-
Hi, Pretty new to Linq and struggling to get my head around it a bit. Here goes.... Survey Tool with a simple (database) hierarchy of: Categories > Sub Categories > Questions > Answers and then another table called SurveyAnswers which basically stores a list of AnswerIds for each survey response. The questions are all multiple choice so you only ever get one SurveyAnswer for each Question (for a particular response). Now, I want to count the number of questions and the number of questions answered for each sub category. In sql:
select c.CategoryId,
sc.SubCategoryId,
COUNT(distinct q.QuestionId) as NumberOfQuestions,
COUNT(sa.AnswerId) as QuestionsAnswered
from Category c
join SubCategory sc
on c.CategoryId = sc.CategoryId
join Question q
on sc.SubCategoryId = q.SubCategoryId
join Answer a
on q.QuestionId = a.QuestionId
left join SurveyAnswer sa
on a.AnswerId = sa.AnswerId
and sa.SurveyResponseId = 1
group by c.CategoryId,
sc.SubCategoryIdAny help with showing me how to do this would be great. I can get to the left outer join bit but the select and group bits defeat me! Thanks.