We help IT Professionals succeed at work.

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

on
Medium Priority
369 Views
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:

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
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,
) ON [PRIMARY]
GO
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)))
``````
Comment
Watch Question

## View Solution Only

Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Senior .Net Developer
CERTIFIED EXPERT

Commented:
case statement will help . . . but as emoreau said, we'll help you out, not do the work for you.

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

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.

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.

Commented:
<< I don't know how to loop, etc in the db.>>
One advice, don't loop.  Or at least I'm out.
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
``````

Commented:
@jogos - thanks.  Let me tinker with that and see if I can do it.
Information Technology Specialist
CERTIFIED EXPERT

Commented:
Given your sample data, what would you expect to see as output?