How to view the total number of participants who got 100 in the quiz??

Question

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

Answer 1

0

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

answered Feb 27 '12 at 06:26 AM

Amardeep
1.3k ● 71 ● 82 ● 86

@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 '12 at 07:13 AM Usman Butt

Thanks, I agreed.

Feb 27 '12 at 07:20 AM Amardeep

(comments are locked)

How to view the total number of participants who got 100 in the quiz??

Follow this question

Related Questions