如何得出显示测验总数，参与者总人数的统计数据? [英] How to come up with statistics that shows the total number of quizzes, total nubmer of participants?

问题描述

我正在开发一个简单的Web应用程序，为用户提供测验.由于我是ASP.NET的新手，所以我关注ASP.net网站上的如何构建Quiz Engine视频"系列.我的数据库设计类似于这些视频中使用的设计.
这是[第一个视频的链接] [1]:
http://www.asp.net/general/videos/lesson- 11-building-a-quiz-engine-1 [ ^ ]

一切正常，但我现在想开发一个查询，以帮助我向管理员显示统计信息，该统计信息显示:

1.系统中的测验总数
2.每个测验的参与者总数
3.总体上以**每天，每周，每月和每年(如果可能)为基础的参与者总数**

为了阐明最后一点，我希望管理员看到以下内容:

1.上周参加的参与者总数是:......
2.上周参加的测验总数为:.........
3.上个月进行的测验总数为:.........

我认为这样的事情对于了解系统的使用情况并向管理层显示系统在工作效率方面非常有用.

数据库的架构:

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:

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')

在这个伟大社区中的一位同事的帮助下，我想到了一个查询，该查询显示了每个测验中的参与者人数.这是查询:

选择Q.QuizID，Q.Title，COUNT(*)个AS用户
FROM dbo.UserQuiz AS UQ
内联接dbo.Quiz AS Q ON Q.QuizID = UQ.QuizID
GROUP BY Q.QuizID，Q.Title

现在，我需要对其进行修改或提出一个新的查询，该查询给出了以上三点.我该怎么办?
[1]:http://www.asp.net/general/videos/lesson-11-building-a-quiz-engine-1

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

推荐答案

试试这个...

Hi,

Try this...

-- 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,

这篇关于如何得出显示测验总数，参与者总人数的统计数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！