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

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

LVL 1
Westside2004Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jogosConnect With a Mentor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
homework season again? start to write your query and we will help you finish it
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
case statement will help . . . but as emoreau said, we'll help you out, not do the work for you.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
jogosCommented:
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
 
Westside2004Author Commented:
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
 
Westside2004Author Commented:
@ 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
 
jogosCommented:
<< I don't know how to loop, etc in the db.>>
One advice, don't loop.  Or at least I'm out.
0
 
Westside2004Author Commented:
@jogos - thanks.  Let me tinker with that and see if I can do it.
0
 
awking00Commented:
Given your sample data, what would you expect to see as output?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.