Solved

Tough SQL Question using AVG() and COUNT()??

Posted on 2012-03-13
9
344 Views
Last Modified: 2012-04-16
I have a set of data that I need to generate some averages on and then
counts and I'm not quite sure how to do it.  I've attached the SQL that will create the table, the columns, and sample data.

Can someone provide me with the SQL to do the following?

I need a count of each company that took our customer feedback survey.
Based on the survey results, each company gets scored in one of the 5
categories, Excellent, Great, Good, Fair, Poor.  The query should only
return these 5 columns with a count in each one.

A sample data set is provided here.  The scoring logic is this:

Question Score calculation is:
The question score is the # of points out of  4

Sub-Topic Score calculation is:

a) Determine the percentage of each question.  Question Score / 4

b) Determine the average of all questions percentages taking into account the weight of each question. A few questions have a weight of 2.00 and must be counted twice, if the weight was 3.00, it must be counted three times, but for the most part the weight for each question is just 1.00

c) Multiply the sub-topic average times the number of points possible for the sub-topic

Topic Score calculation is:
All sub-topic scores added together.

Lastly, just generate the Topic Score percentage by dividing the Topic Score by the Total Possible Points for the topic to determine which bucket they fall under.

If the "Topic Score Percentage", is 90% or greater, then count the customer as "Excellent"
If the "Topic Score Percentage" is between 70% and 90%, then count the customer as "Great"
If the "Topic Score Percentage" is between 50% and 70%, then count the customer as "Good"
If the "Topic Score Percentage" is between 30% and 50%, then count the customer as "Fair"
If the "Topic Score Percentage" is between 0% and 30%, then count the customer as "Poor"

Any help appreciated

The SQL is attached in a script, but I've pasted it here as well:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SurveyResults](
	[TopicID] [int] NULL,
	[TopicName] [varchar](100) NULL,
	[TopicPoints] [numeric](18, 0) NULL,
	[SubTopicID] [int] NULL,
	[SubTopicName] [nvarchar](255) NULL,
	[SubTopicPoints] [numeric](18, 0) NULL,
	[QuestionID] [int] NULL,
	[Question] [varchar](500) NULL,
	[SurveyID] [int] NULL,
	[AnswerScore] [decimal](10, 2) NULL,
	[AnswerWeight] [numeric](18, 2) NULL,
	[AnswerPercentage] [decimal](10, 4) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SurveyResults] ([TopicID], [TopicName], [TopicPoints], [SubTopicID], [SubTopicName], [SubTopicPoints], [QuestionID], [Question], [SurveyID], [AnswerScore], [AnswerWeight], [AnswerPercentage]) VALUES (10, N'Customer Appreciation', CAST(250 AS Numeric(18, 0)), 1, N'Customer Focus', CAST(100 AS Numeric(18, 0)), 86, N'Did we do a good job', 832, CAST(4.00 AS Decimal(10, 2)), CAST(1.00 AS Numeric(18, 2)), CAST(1.0000 AS Decimal(10, 4)))
INSERT [dbo].[SurveyResults] ([TopicID], [TopicName], [TopicPoints], [SubTopicID], [SubTopicName], [SubTopicPoints], [QuestionID], [Question], [SurveyID], [AnswerScore], [AnswerWeight], [AnswerPercentage]) VALUES (10, N'Customer Appreciation', CAST(250 AS Numeric(18, 0)), 1, N'Customer Focus', CAST(100 AS Numeric(18, 0)), 86, N'Did we do a good job', 833, CAST(4.00 AS Decimal(10, 2)), CAST(1.00 AS Numeric(18, 2)), CAST(1.0000 AS Decimal(10, 4)))
INSERT [dbo].[SurveyResults] ([TopicID], [TopicName], [TopicPoints], [SubTopicID], [SubTopicName], [SubTopicPoints], [QuestionID], [Question], [SurveyID], [AnswerScore], [AnswerWeight], [AnswerPercentage]) VALUES (10, N'Customer Appreciation', CAST(250 AS Numeric(18, 0)), 24, N'Customer Loyalty', CAST(50 AS Numeric(18, 0)), 89, N'Were you satisfied', 833, CAST(3.25 AS Decimal(10, 2)), CAST(1.00 AS Numeric(18, 2)), CAST(0.8125 AS Decimal(10, 4)))
INSERT [dbo].[SurveyResults] ([TopicID], [TopicName], [TopicPoints], [SubTopicID], [SubTopicName], [SubTopicPoints], [QuestionID], [Question], [SurveyID], [AnswerScore], [AnswerWeight], [AnswerPercentage]) VALUES (10, N'Customer Appreciation', CAST(250 AS Numeric(18, 0)), 24, N'Customer Loyalty', CAST(50 AS Numeric(18, 0)), 89, N'Were you satisfied', 832, CAST(3.25 AS Decimal(10, 2)), CAST(1.00 AS Numeric(18, 2)), CAST(0.8125 AS Decimal(10, 4)))
INSERT [dbo].[SurveyResults] ([TopicID], [TopicName], [TopicPoints], [SubTopicID], [SubTopicName], [SubTopicPoints], [QuestionID], [Question], [SurveyID], [AnswerScore], [AnswerWeight], [AnswerPercentage]) VALUES (10, N'Customer Appreciation', CAST(250 AS Numeric(18, 0)), 24, N'Customer Loyalty', CAST(50 AS Numeric(18, 0)), 90, N'Would you visit us again?', 832, CAST(2.00 AS Decimal(10, 2)), CAST(2.00 AS Numeric(18, 2)), CAST(0.5000 AS Decimal(10, 4)))

Open in new window

0
Comment
Question by:Westside2004
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37715851
homework season again? start to write your query and we will help you finish it
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37715915
case statement will help . . . but as emoreau said, we'll help you out, not do the work for you.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37715936
This is the finishing :) you get your percentage together
CASE WHEN Percentage >= 90 THEN 'Excellent'
 WHEN Percentage >= 70 THEN 'Great'
...
ELSE 'Poor'
END

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:Westside2004
ID: 37715950
No, this is not homework at all.  I'm attempting to do this in the databases opposed to my front-end app that's why I'm asking for assistance .  I know what needs to happen, but I'm struggling doing it in all SQL.  I don't know how to loop, etc in the db.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 37715956
@ jogos - Thanks for the reply, the part I'm struggling with is the calculations and the adding part.  I understand that once the percentage is determined one can do a CASE statement.

@ emoreau - I just included the SQL script from my database to help so anyone who was willing wouldn't have to assume what I was trying to do and it make it easy as I can for them to help me.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37716011
<< I don't know how to loop, etc in the db.>>
One advice, don't loop.  Or at least I'm out.
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37716196
To get you on the way. To make it yourself easy do first your row by row calculations in a separate query. Second you only have to 'group by' - treated.
The final result do it on the result of that subquery or cte

 
Options
1. Subquery
select x.*
from (
           select --TODO do here your record by record calculations like a)
            from [dbo].[SurveyResults]
           ) as x

2 use Common Table Expression
http://msdn.microsoft.com/en-us/library/ms175972.aspx


;with sur_cte ( , , , )  -- TODO name the columns of what you return from the rec/rec calculations
as (
select --TODO do here your record by record calculations like a)
from [dbo].[SurveyResults]
)
select *
from sur_cte 

Open in new window

0
 
LVL 1

Author Comment

by:Westside2004
ID: 37716297
@jogos - thanks.  Let me tinker with that and see if I can do it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37720739
Given your sample data, what would you expect to see as output?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question