x

How to get the number of respondents for each question choice?

Hello everybody,

I have a survey that I need to design a database for it to store its results and retrieve the results using some queries.

The current database design that I have is as following:

 Respondents Table: RespondentID, Name, OrgCode  
 Department Table: OrgCode, DepartmentName  
 Category Table: CategoryID, CategoryName  
 SubCategory Table: SubCategoryID, SubCategoryName, CategoryID  
 Questions Table: QuestionID, QuestionDesc, CategoryID, SubCategoryID  
 SubQuestions Table: SubQuestionID, SubQuestionDesc, QuestionID  
 Answers Table: AnswerID, AnswerDesc, QuestionID, SubQuestionID  
 CompleteSurvey Table: ID, RespondentID, CategoryID, AnswerID  

An example of this survey:

 Category I  
     SubCategory A  
     Question 1: what do you think about the following service  
         SubQuestion 1: Service #1   
             (Strongly Agree, Agree, Disagree, Strongly Disagree)  
         SubQuestion 2: Service #2   
             (Strongly Agree, Agree, Disagree, Strongly Disagree)  

Let us assume that there are three employees answered this question Now, I want to write a query that shows the number of respondents who said Strongly Agree, Agree, Disagree and Strongly Agree in each question or subquestion. Is it doable by this database design? If yes, could you please help me with this query? Also, do you think there is other designs that are much simpler than the above design? If yes, could you please recommend me with one of them? Database Design: /***** Object: Table [dbo].[Departments] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Departments] ( [OrgCode] [float] NOT NULL , [DepartmentName] [nvarchar](MAX) NOT NULL , CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED ( [OrgCode] 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].[Category] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Category] ( [CategoryID] [int] IDENTITY(1, 1) NOT NULL , [CategoryName] [nvarchar](50) NOT NULL , CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [CategoryID] 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].[SubCategory] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SubCategory] ( [SubCategoryID] [int] IDENTITY(1, 1) NOT NULL , [SubCategoryName] [nvarchar](50) NOT NULL , [CategoryID] [int] NOT NULL , CONSTRAINT [PK_SubCategory] PRIMARY KEY CLUSTERED ( [SubCategoryID] 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].[Respondents] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Respondents] ( [RespondentID] [nvarchar](50) NOT NULL , [Name] [varchar](50) NULL , [OrgCode] [float] NOT NULL , CONSTRAINT [PK_Respondents] PRIMARY KEY CLUSTERED ( [RespondentID] 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].[Questions] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Questions] ( [QuestionID] [int] IDENTITY(1, 1) NOT NULL , [QuestionDesc] [nvarchar](MAX) NOT NULL , [CategoryID] [int] NULL , [SubCategoryID] [int] NULL , CONSTRAINT [PK_Questions] 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 /****** Object: Table [dbo].[CompleteSurvey] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CompleteSurvey] ( [ID] [int] IDENTITY(1, 1) NOT NULL , [RespondentID] [nvarchar](50) NOT NULL , [CategoryID] [int] NOT NULL , [AnswerID] [int] NOT NULL , CONSTRAINT [PK_CompleteSurvey] PRIMARY KEY CLUSTERED ( [ID] 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].[SubQuestions] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SubQuestions] ( [SubQuestionID] [int] IDENTITY(1, 1) NOT NULL , [SubQuestionDesc] [nvarchar](MAX) NOT NULL , [QuestionID] [int] NULL , CONSTRAINT [PK_SubQuestions] PRIMARY KEY CLUSTERED ( [SubQuestionID] 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].[Answers] Script Date: 05/20/2012 07:26:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Answers] ( [AnswerID] [int] IDENTITY(1, 1) NOT NULL , [AnswerDesc] [nvarchar](MAX) NOT NULL , [QuestionID] [int] NULL , [SubQuestionID] [int] NULL , CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED ( [AnswerID] 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: ForeignKey [FK_SubCategory_Category] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[SubCategory] WITH CHECK ADD CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID]) REFERENCES [dbo].[Category] ([CategoryID]) GO ALTER TABLE [dbo].[SubCategory] CHECK CONSTRAINT [FK_SubCategory_Category] GO /****** Object: ForeignKey [FK_Respondents_Departments] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[Respondents] WITH CHECK ADD CONSTRAINT [FK_Respondents_Departments] FOREIGN KEY([OrgCode]) REFERENCES [dbo].[Departments] ([OrgCode]) GO ALTER TABLE [dbo].[Respondents] CHECK CONSTRAINT [FK_Respondents_Departments] GO /****** Object: ForeignKey [FK_Questions_Category] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[Questions] WITH CHECK ADD CONSTRAINT [FK_Questions_Category] FOREIGN KEY([CategoryID]) REFERENCES [dbo].[Category] ([CategoryID]) GO ALTER TABLE [dbo].[Questions] CHECK CONSTRAINT [FK_Questions_Category] GO /****** Object: ForeignKey [FK_Questions_SubCategory] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[Questions] WITH CHECK ADD CONSTRAINT [FK_Questions_SubCategory] FOREIGN KEY([SubCategoryID]) REFERENCES [dbo].[SubCategory] ([SubCategoryID]) GO ALTER TABLE [dbo].[Questions] CHECK CONSTRAINT [FK_Questions_SubCategory] GO /****** Object: ForeignKey [FK_CompleteSurvey_Respondents] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[CompleteSurvey] WITH CHECK ADD CONSTRAINT [FK_CompleteSurvey_Respondents] FOREIGN KEY([RespondentID]) REFERENCES [dbo].[Respondents] ([RespondentID]) GO ALTER TABLE [dbo].[CompleteSurvey] CHECK CONSTRAINT [FK_CompleteSurvey_Respondents] GO /****** Object: ForeignKey [FK_SubQuestions_Questions] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[SubQuestions] WITH CHECK ADD CONSTRAINT [FK_SubQuestions_Questions] FOREIGN KEY([QuestionID]) REFERENCES [dbo].[Questions] ([QuestionID]) GO ALTER TABLE [dbo].[SubQuestions] CHECK CONSTRAINT [FK_SubQuestions_Questions] GO /****** Object: ForeignKey [FK_Answers_Questions] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[Answers] WITH CHECK ADD CONSTRAINT [FK_Answers_Questions] FOREIGN KEY([QuestionID]) REFERENCES [dbo].[Questions] ([QuestionID]) GO ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_Questions] GO /****** Object: ForeignKey [FK_Answers_SubQuestions] Script Date: 05/20/2012 07:26:11 ******/ ALTER TABLE [dbo].[Answers] WITH CHECK ADD CONSTRAINT [FK_Answers_SubQuestions] FOREIGN KEY([SubQuestionID]) REFERENCES [dbo].[SubQuestions] ([SubQuestionID]) GO ALTER TABLE [dbo].[Answers] CHECK CONSTRAINT [FK_Answers_SubQuestions] GO

more ▼

asked May 20, 2012 at 04:29 AM in Default

avatar image

essence388
21 11 11 14

Crud. I tried to clean up the formatting & now it's a mess.

May 21, 2012 at 01:14 PM Grant Fritchey ♦♦

Four attempts to clean it up... I surrender.

May 21, 2012 at 01:15 PM Grant Fritchey ♦♦

I tried 3 or 4 times over the weekend, I even took it all out and pasted back in. There is a problem there somewhere...

May 21, 2012 at 02:11 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
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:

x84
x57

asked: May 20, 2012 at 04:29 AM

Seen: 909 times

Last Updated: May 21, 2012 at 02:11 PM

Copyright 2017 Redgate Software. Privacy Policy