asked
How to view the total number of participants who got 100 in the quiz??
Hello everybody, 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 What I want now is to show an indicator in the Result page of each quiz that will show to the user the number of participants in the Employee's Division who got 100 from the first time of taking the quiz. (so if the user takes the quiz again, his second try will not be considered in this query). **So how to do that?** My Query which is not correct is: SELECT dbo.Divisions.DivisionShortcut, COUNT(dbo.UserQuiz.Username) AS [Number of Participants], dbo.UserQuiz.Score 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 GROUP BY dbo.Divisions.DivisionShortcut, dbo.UserQuiz.Score And here is the Database Schema: USE [psspdbTest] GO /****** Object: Table [dbo].[Divisions] Script Date: 02/26/2012 09:33:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Divisions]( [SapCode] [float] NOT NULL, [DivisionShortcut] [varchar](10) NOT NULL, [DivisionName] [varchar](max) NOT NULL, CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED ( [SapCode] 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 /****** Object: Table [dbo].[Quiz] Script Date: 02/26/2012 09:33:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Quiz]( [QuizID] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](max) NOT NULL, [IsSent] [bit] 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 /****** Object: Table [dbo].[employee] Script Date: 02/26/2012 09:33:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[employee]( [Name] [nvarchar](max) NOT NULL, [Username] [nvarchar](255) NOT NULL, [JobTitle] [nvarchar](max) NOT NULL, [BadgeNo] [float] NOT NULL, [EmpOrgType] [float] NOT NULL, [DivisionCode] [float] NOT NULL, CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED ( [Username] 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 /****** Object: Table [dbo].[UserQuiz] Script Date: 02/26/2012 09:33:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserQuiz]( [UserQuizID] [int] IDENTITY(1,1) NOT NULL, [QuizID] [int] NOT NULL, [DateTimeComplete] [smalldatetime] NOT NULL, [Score] [float] NOT NULL, [Username] [nvarchar](255) NOT NULL, CONSTRAINT [PK_UserQuiz] PRIMARY KEY CLUSTERED ( [UserQuizID] 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 /****** Object: Default [DF_Quiz_IsSent] Script Date: 02/26/2012 09:33:25 ******/ ALTER TABLE [dbo].[Quiz] ADD CONSTRAINT [DF_Quiz_IsSent] DEFAULT ((0)) FOR [IsSent] GO /****** Object: ForeignKey [FK_employee_Divisions] Script Date: 02/26/2012 09:33:25 ******/ ALTER TABLE [dbo].[employee] WITH CHECK ADD CONSTRAINT [FK_employee_Divisions] FOREIGN KEY([DivisionCode]) REFERENCES [dbo].[Divisions] ([SapCode]) GO ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [FK_employee_Divisions] GO /****** Object: ForeignKey [FK_UserQuiz_employee] Script Date: 02/26/2012 09:33:25 ******/ ALTER TABLE [dbo].[UserQuiz] WITH CHECK ADD CONSTRAINT [FK_UserQuiz_employee] FOREIGN KEY([Username]) REFERENCES [dbo].[employee] ([Username]) GO ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_employee] GO /****** Object: ForeignKey [FK_UserQuiz_Quiz] Script Date: 02/26/2012 09:33:25 ******/ ALTER TABLE [dbo].[UserQuiz] WITH CHECK ADD CONSTRAINT [FK_UserQuiz_Quiz] FOREIGN KEY([QuizID]) REFERENCES [dbo].[Quiz] ([QuizID]) GO ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Quiz] GO
Will be easy to give accurate query if you could give some sample output data which you require, you could also try to get your result as per given below example-
WITH CTE (rownum,QuizID,Username,SapCode,DivisionShortcut,Score,DateTimeComplete)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY U.Username,QuizID ORDER BY DateTimeComplete) rownum,
QuizID,U.Username,SapCode,DivisionShortcut,Score,DateTimeComplete FROM UserQuiz U
INNER JOIN employee E ON U.Username=E.Username
INNER JOIN Divisions D ON E.DivisionCode =D.SapCode
)
SELECT Username,DivisionShortcut,COUNT(Username) AS [Number of Participants] ,score FROM CTE
WHERE rownum=1 AND Score=100
GROUP BY Username,DivisionShortcut,score
@Amardeep +1. I know that it is very difficult to come up with a precise snippet without sample data and desired output, and I appreciate your work, but having Username in the GROUP BY clause will definitely have ill-effects on the output. So I guess you may need to change your code a bit. Moreover, the OP can work out better, whether sorting should be done on DateTimeComplete field OR UserQuizID field, while numbering the rows through ROW_NUMBER function.
3 People are following this question.
