Avatar of Gus Koutsivitis
Gus Koutsivitis
Flag for United States of America asked on

Query to get the Number of users for each Topic.

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!
Microsoft DevelopmentMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
harfang

8/22/2022 - Mon
ValentinoV

Would it be possible to post a small script that creates those tables and adds some sample data to them?  Data types and field content are really important for this type of question...
ASKER CERTIFIED SOLUTION
harfang

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gus Koutsivitis

ASKER
great!  They both worked.  Seems the results are correct.  The second query I get an error with the "First" function.  I replaced it with MAX or MIN and it worked!

thanks for your help!!
harfang

I'm glad it worked, success with your project! — (°v°)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23