x

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
more ▼

asked Feb 26, 2012 at 06:31 AM in Default

avatar image

essence388
21 11 11 14

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Feb 27, 2012 at 06:26 AM

avatar image

Sharma
1.3k 88 91 94

@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.

Feb 27, 2012 at 07:13 AM Usman Butt

Thanks, I agreed.

Feb 27, 2012 at 07:20 AM Sharma
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x738
x425

asked: Feb 26, 2012 at 06:31 AM

Seen: 1210 times

Last Updated: Feb 27, 2012 at 07:20 AM

Copyright 2016 Redgate Software. Privacy Policy