We help IT Professionals succeed at work.
Get Started

Query to get the Number of users for each Topic.

348 Views
Last Modified: 2012-07-10
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

Open in new window


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

Open in new window



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

Open in new window



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!
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE