How to come up with statistics that shows the total number of quizzes, total nubmer of participants?

Question

0.00/5 (No votes)

See more:

I am developing a simple web application that provides the users with quizzes. Since I am new to ASP.NET world, I am following the How to build Quiz Engine Video series on ASP.net website. My database design is to similar to the design used in these videos.
This is the [link of the first video][1]:
http://www.asp.net/general/videos/lesson-11-building-a-quiz-engine-1[^]

Everything works fine with me, but I want now to develop a query that helps me to display a statistics to the Admin which shows:

1. Total number of quizzes in the system
2. Total number of participants in each quiz
3. Total number of participants in general in **daily basis, weekly basis, monthly basis and yearly basis (if possible)**

To clarify the last point, I want the admin to see the following:

1. total number of participant druing last week was: ......
2. total number of taken quizzes during last week was: .........
3. total number of taken quizzes during last month was: .........

I think something like this is very useful to know the usage of the system and to show the management how the system is efficient in the copmany.

The schema of the database:

SQL

CREATE TABLE [dbo].[Quiz](
       [QuizID] [int] IDENTITY(1,1) NOT NULL,
       [Title] [varchar](max) NOT NULL,
       [Description] [varchar](max) NULL,
    CONSTRAINT [PK_Quiz] PRIMARY KEY CLUSTERED
   (
       [QuizID] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   SET ANSI_PADDING OFF
   GO
   SET IDENTITY_INSERT [dbo].[Quiz] ON
   INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (6, N'Safety', N'General Safety Test')
   INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (7, N'my title', N'my description')
   INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (9, N'General Safety Quiz2', N'Testing')
   INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (10, N'General Safety Quiz3', N'Testing #2')
   SET IDENTITY_INSERT [dbo].[Quiz] OFF


   /****** Object:  Table [dbo].[Question]    Script Date: 11/17/2011 00:44:38 ******/

   CREATE TABLE [dbo].[Question](
       [QuestionID] [int] IDENTITY(1,1) NOT NULL,
       [Question] [varchar](max) NOT NULL,
       [Answer1] [varchar](max) NOT NULL,
       [Answer2] [varchar](max) NOT NULL,
       [Answer3] [varchar](max) NOT NULL,
       [Answer4] [varchar](max) NOT NULL,
       [CorrectAnswer] [tinyint] NOT NULL,
       [AnswerExplanation] [varchar](max) NULL,
       [QuestionOrder] [tinyint] NOT NULL,
       [QuizID] [int] NOT NULL,
    CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
   (
       [QuestionID] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   SET ANSI_PADDING OFF
   GO
   SET IDENTITY_INSERT [dbo].[Question] ON
   INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (4, N'What is your name?', N'Mohammed ', N'Ali', N'Hassan', N'Husain', 1, N'My Name', 1, 6)
   INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (7, N'What is the definition of Safety?', N'Being Safe', N'Being in danger', N'Be careful', N'be careless', 1, N'Nothing', 1, 9)
   INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (8, N'What is the definition of Safety? ', N'Being Safe', N'Being Careless', N'Being Careful', N'Being in Dangerous', 1, N'Nothing to say', 1, 10)
   SET IDENTITY_INSERT [dbo].[Question] OFF
   /****** Object:  Table [dbo].[UserQuiz]    Script Date: 11/17/2011 00:44:38 ******/
   CREATE TABLE [dbo].[UserQuiz](
       [UserQuizID] [int] NULL,
       [QuizID] [int] NOT NULL,
       [DateTimeComplete] [smalldatetime] NOT NULL,
       [Score] [tinyint] NOT NULL,
       [Username] [nvarchar](256) NOT NULL
   ) ON [PRIMARY]
   GO
   INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F02D8 AS SmallDateTime), 100, N'SMP\ALMARHMS')
   INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F02E3 AS SmallDateTime), 50, N'SMP\ALMARHMS')
   INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F0333 AS SmallDateTime), 50, N'SMP\ALMARHMS')
   INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 7, CAST(0x9F8F0335 AS SmallDateTime), 100, N'SMP\ALMARHMS')

By help from one of the guys here in this great community, I came up with one query that shows the number of participants in each quiz. This is the query:

SELECT Q.QuizID, Q.Title, COUNT(*) AS Users
FROM dbo.UserQuiz AS UQ
INNER JOIN dbo.Quiz AS Q ON Q.QuizID = UQ.QuizID
GROUP BY Q.QuizID, Q.Title

Now I need to modify it or to come up withe new query that gives me the above three points. How can I do that?
[1]: http://www.asp.net/general/videos/lesson-11-building-a-quiz-engine-1

[Edit - Turned on HTML so code block would work, created video link, spelling]

Posted 17-Nov-11 7:23am

matrix388

Updated 17-Nov-11 8:16am

Kschuler

v4

Add a Solution

Comments

Kschuler 17-Nov-11 14:17pm

When writing a question, there is a check box at the bottom that if checked will turn off all html formatting. If you want to use the code block formatting or add a link you'll want to make sure that ISN'T checked. I edited your question and turned formatting back on.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Al Moje · Accepted Answer · 2011-11-17T15:17:00

Hi,

Try this...

C#

-- Monthly
SELECT Q.QuizID, DateName(Month,(UQ.DateTimeComplete)) As [Month], Q.Title
FROM 
dbo.UserQuiz AS UQ 
INNER JOIN dbo.Quiz AS Q 
ON Q.QuizID = Q.QuizID
GROUP BY Q.QuizID, Q.Title,DateName(Month,(UQ.DateTimeComplete))
--Yearly
SELECT Q.QuizID, DateName(Year,(UQ.DateTimeComplete)) as [Year], Q.Title
FROM 
dbo.UserQuiz AS UQ 
INNER JOIN dbo.Quiz AS Q 
ON Q.QuizID = Q.QuizID
GROUP BY Q.QuizID, Q.Title,DateName(Year,(UQ.DateTimeComplete))
--Daily
SELECT Q.QuizID, DateName(WeekDay,(UQ.DateTimeComplete)) As [Week], Q.Title
FROM 
dbo.UserQuiz AS UQ 
INNER JOIN dbo.Quiz AS Q 
ON Q.QuizID = Q.QuizID
GROUP BY Q.QuizID, Q.Title,DateName(WeekDay,(UQ.DateTimeComplete))

Remember to vote if help...

Regards,

How to come up with statistics that shows the total number of quizzes, total nubmer of participants?

1 solution

Solution 1

Add your solution here

Preview 0

How to come up with statistics that shows the total number of quizzes, total nubmer of participants?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us