troubleshooting Question

Query to get the Number of users for each Topic.

Avatar of kouts1
kouts1Flag for United States of America asked on
Microsoft DevelopmentMicrosoft SQL Server 2008
4 Comments1 Solution349 ViewsLast Modified:
Hi all,

What I need is a query that will show the total Users who have Completed each Topic/Question.
these are my tables:

answers
ansID, questionID, userName, AnsStatus, ans1, ans2, ans3
               
questions
qID, qNum, q1, q2, q3

Topics
topicID, topicTitle, topicType, case_stem, modID, notesToOwner, topicOwner, categories, DueDate, quesID, topicStatus
 
Note: modID i another table where I grab the module name that is on a form with a dropdown.  I don't care about the module name for this solution.
         
                                           
Basically the way this works is that all students are presented with topics and questions.  The only time I capture the student username is after they have submitted the topic and answered the questions.  I am using Active Directory FBA login.  I didn't want to enumerate hundreds of users who log on and and then add them to SQL Table ,so I came up with this system.  Not great but it works.
So all students who log onto the application see all topics and questions.  

This is what i have that grabs the number of completed answers by user:

SELECT uName,COUNT(anID) As "TotalCount" FROM tbl_answers
Where ansStatus = 'Completed'
GROUP BY uName

then I have this query that when user is selected from the above query I get this:

SELECT topicID, topicTitle, case_stem, topicType, qID, qNum, q1, q2, q3, questionID, anID, uName, an1, an2, an3
FROM  topic_Unit, tbl_questions, tbl_answers
WHERE tbl_answers.questionID = tbl_questions.qID
AND tbl_answers.ansStatus = 'Completed'
AND topic_Unit.quID = tbl_questions.qID
AND tbl_answers.uName = @uName


This query also gives me the total topics that a user has remaining:

SELECT topicID, topicTitle, modID, dueDate, moduleName, quID
FROM topic_Unit, tbl_Modules
WHERE tbl_Modules.moduleID = topic_Unit.modID
AND topic_Unit.dueDate  >='6/27/2012' AND topic_Unit.dueDate <= '7/24/2012' 
and topic_Unit.quID not in (select questionID from tbl_answers where uName= @user)
AND topic_Unit.topicType = 'conf' AND topic_Unit.status = 'Available'
Order By topic_Unit.dueDate Asc


the above works great, but now I need to show
topicTitle: | total users |  Status


I want to group by topicTitle (distinct) if possible to get all the topics by name.
Some title will be dupes.


thanks for the help!
ASKER CERTIFIED SOLUTION
harfang

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros