根据计数计算平均值 [英] Computing the averages based on counts

问题描述

需要按比例计算和分配一个等级的值



表格如下：





代码名称respond1 respond2 respnond3 respond4

------------------------- ----------------------------------

001简快乐满意快乐中立

002保罗满意中立

003 ama开心快乐
$ b $ 00 004 jon中性不开心
$ b $ 00 005宠物不开心中性




选择代码，名称，案例

当respond1 ='happy'然后100

当respond1 ='满意'然后50

当respond1 ='中立'然后25

当respond1 ='unhappy'然后0

end asRESULT1，

case

当response2 ='hap py'然后100

当respond2 ='满意'然后50

当respond2 ='中立'然后25

当respond2 ='unhappy'时然后0

结束为RESULT2，

案例

当respond3 ='happy'然后100

时response3 ='满意'然后50

当respond3 ='中立'然后25

当respond3 ='unhappy'然后0

结束为结果3，

案例

当respond4 ='happy'然后100

当respond4 ='满意'然后50

当respond4 ='中立'然后25

当respond4 ='不开心'然后0

结束为RESULT4，



决赛桌是



代码，na我，result1，result2，result3，result4，





请求



1.我们有一些 respond1，respond2，repsond3，respond4 为空，因此在计算他们应该被淘汰的平均值的过程中会花费
。





代码

001 因为4响应因此计算平均值应该 4个计数

002 因为3个响应因此平均值的计算应该 3个计数

003 因为2回应所以平均值的计算应该是 2个计数

004 因为2响应因此平均值的计算应该在 2个计数

005 ，因为2响应因此平均值的计算应该 2个计数



2.如果result1，result2，result3，result4的平均值，结果4：



介于80和100之间应该将最终分配为'DELIGHTED'



介于60和80之间，应该指定最终'好''



介于40和60之间，应将最终指定为'SATISF'



介于20和40之间，应将最终指定为'UNSATIS'



介于10和20之间时，应将最终指定为'UNHAPPY'



介于0和小于10之间应该将最终分配为'坏'





我如何做到这一点。



请协助



谢谢



什么我试过了：



这是我使用CASE条件

解决方案

<的代码blockquote>首先，不要这样做：你是重复信息，给自己带来可能的问题 - 只需要一个错误的关键系统，你的系统就会失败。

相反，设置第二个表回复：

 ID INT 
说明NVARCHAR 

回复数据将是：

 ID描述
 0不快乐
 25中性
 50满意
 100快乐

您将ID值用作原始表中的外键。

您使用JOIN当你需要人类可读的值时组合表格，上面的SELECT变得微不足道。

Need to calculate and assign a value on a scale as follows

The table is as follows :


code name respond1 respond2 respnond3 respond4
-----------------------------------------------------------
001 jane happy satisfied happy neutral
002 paul happy satisfied neutral
003 ama happy happy
004 jon neutral unhappy
005 pet unhappy neutral


select code, name, case
when respond1='happy' then 100
when respond1='satisfactory' then 50
when respond1='neutral' then 25
when respond1='unhappy' then 0
end as "RESULT1",
case
when respond2='happy' then 100
when respond2='satisfactory' then 50
when respond2='neutral' then 25
when respond2='unhappy' then 0
end as "RESULT2",
case
when respond3='happy' then 100
when respond3='satisfactory' then 50
when respond3='neutral' then 25
when respond3='unhappy' then 0
end as "RESULT3",
case
when respond4='happy' then 100
when respond4='satisfactory' then 50
when respond4='neutral' then 25
when respond4='unhappy' then 0
end as "RESULT4",

final table is

code,name,result1,result2,result3,result4,


Request

1. We have some of the respond1, respond2,repsond3, respond4 empty hence in
the process of computing the average they should be eliminated.


code
001 as 4 responds hence the computation of the average should be on 4 counts
002 as 3 responds hence the computation of the average should be on 3 counts
003 as 2 responds hence the computation of the average should be on 2 counts
004 as 2 responds hence the computation of the average should be on 2 counts
005 as 2 responds hence the computation of the average should be on 2 counts

2. If the average of the result1,result2,result3,result4 :

is between 80 and 100 inclusive it should assign the final as 'DELIGHTED'

is between 60 and and less than 80 it should assign the final as 'GOOD'

is between 40 and and less than 60 it should assign the final as 'SATISF'

is between 20 and and less than 40 it should assign the final as 'UNSATIS'

is between 10 and and less than 20 it should assign the final as 'UNHAPPY'

is between 0 and and less than 10 it should assign the final as 'BAD'


How do I accomplish this.

Please assist

Thanks

What I have tried:

This are codes I have worked on using the CASE condition

解决方案

First off, don't do it like that: you are duplicating information, and giving yourself possible problems - all it takes is one miss-keyed enytry and you system fails.
Instead, set up a second table Responses:

ID            INT
Description   NVARCHAR

The Responses data would be:

ID    Description
0     Unhappy
25    Neutral
50    Satisfactory
100   Happy

And you use the ID value as a Foreign Key into your original table.
You use a JOIN to combine the table when you need human readable values, and your SELECT for the above becomes trivial.

这篇关于根据计数计算平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！