question

essence388 avatar image
essence388 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)?**
sql-server-2008querytsqlsql-server-express
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is wrong with your script? You should have added sample data and desired output to get better help.
0 Likes 0 ·
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?
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)
2 Likes 2 ·
Thanks, I really appreciate your help. Is it possible to let it showing the last two quizzes?
0 Likes 0 ·
essence388 avatar image
essence388 answered
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?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.