asked
How to show the number of participants in the last sent/taken quiz?
I am developing a simple Quiz Engine similar to that one in the
ASP.NET website. I have the following database design: > User Table: Username, Name, DivisionCode... etc > > Division Table: SapCode, Division > > Quiz Table: QuizID, Title, IsSent, Description > > UserQuiz: UserQuizID, QuizID, DateTimeComplete, Score, Username Every week, the employees in my company will recieve an email notification to participate in the new quiz that is avaliable in the system. The quiz that will be sent to the employees will have (True) as a value of IsSent attribute and this is what discriminates it from the other quizzes which are not being sent to the employees. What I want now is showing the number of participants in the last taken quiz since there is only one quiz will be sent each week. My Query that shows the total number of participants in all taken quizzes: SELECT dbo.Divisions.DivisionShortcut, COUNT(DISTINCT dbo.UserQuiz.Username) AS [Number of Participants], dbo.Quiz.QuizID FROM dbo.Divisions INNER JOIN dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode INNER JOIN dbo.UserQuiz ON dbo.employee.Username = dbo.UserQuiz.Username INNER JOIN dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID WHERE (dbo.Quiz.IsSent = 1) GROUP BY dbo.Divisions.DivisionShortcut, dbo.Quiz.QuizID **So how to fix it to show only the number of participants in the last taken quiz (the quiz that is sent)?**
What is wrong with your script? You should have added sample data and desired output to get better help.
the wrong with it, is showing all the quizzes tha are sent to the participants. For the Admin, it is just important to see the number of participants in the Last quiz only. So How I fix this query to show the results for the last quiz only?
Since you did not add any sample data etc., the following is a guesswork SELECT dbo.Divisions.DivisionShortcut, COUNT(DISTINCT dbo.UserQuiz.Username) AS [Number of Participants], dbo.Quiz.QuizID FROM dbo.Divisions INNER JOIN dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode INNER JOIN dbo.UserQuiz ON dbo.employee.Username = dbo.UserQuiz.Username INNER JOIN dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID WHERE [Quiz].[QuizID] = (SELECT MAX([QuizID]) FROM dbo.Quiz WHERE (IsSent = 1)) GROUP BY dbo.Divisions.DivisionShortcut, dbo.Quiz.QuizID
Try changing the WHERE clause to something like this WHERE [Quiz].[QuizID] IN (SELECT TOP 2 [QuizID] FROM dbo.Quiz WHERE (IsSent = 1) ORDER BY [QuizID] DESC)
Thanks, I really appreciate your help. Is it possible to let it showing the last two quizzes?
I really appreciate your help. I have only one more problem which is the query should display zeroes for the divisions that don't have any participants in one of the quizzes, so how to fix this problem to display zeroes instead of displaying only the divisions that have participants?
It should have been pretty easy. Use Left Join instead of the inner join. Seek more help from [BOL][1]. [1]:
http://msdn.microsoft.com/en-us/library/ms187518.aspx
3 People are following this question.
