asked
How to come up with statistics that shows the total number of quizzes, total nubmer of participants?
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]: 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 sechma of the database: 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
After re-reading the question, I'm not too certain on whether this will do what you really want, but it should get you started. SELECT Q.QuizID, Q.Title, COUNT(UQ.QuizID) AS Users, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(week, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastWeek, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(month, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastMonth, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(year, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastYear FROM dbo.Quiz AS Q LEFT JOIN dbo.UserQuiz AS UQ ON Q.QuizID = UQ.QuizID GROUP BY Q.QuizID, Q.Title The main thing to note is that I use a "trick" to count the data. As luck would have it, I blogged about this very idea about an hour ago: [When COUNT() isn't the only way to count][1]. You need to pay attention to the date logic. I am assuming that the `DATEPART` calculations are ok for what you want. This may not be the case and can be changed as needed. If you need further help, or this query isn't enough, let me know. [1]:
http://williamdurkin.wordpress.com/2011/11/17/when-count-isnt-the-only-way-to-count/
