Solved

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

Posted on 2012-03-13
9
343 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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