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 '12 at 06:31 AM in Default

essence388 gravatar image

essence388
21 11 11 13

(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 '12 at 06:26 AM

Amardeep gravatar image

Amardeep
1.3k 84 88 89

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x562
x362

asked: Feb 26 '12 at 06:31 AM

Seen: 833 times

Last Updated: Feb 27 '12 at 07:20 AM