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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

É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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
jogosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Westside2004Author Commented:
@jogos - thanks.  Let me tinker with that and see if I can do it.
0
awking00Information Technology SpecialistCommented:
Given your sample data, what would you expect to see as output?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.