如何在Sql Server中使用以下查询找到Bfid值的平均值？ [英] How to find the average of Bfid Value using thhe below query in Sql Server?

问题描述

我的查询如下;

My query as follows;

 select distinct r.rate As Rate,f.bfid as BFid,
CASE  WHEN r.rate = '1' THEN '100'
 WHEN r.rate = '2' THEN '75' 
WHEN r.rate = '3' THEN '50'
 WHEN r.rate = '4' THEN '25' END AS Translated_Rate 
FROM bthfac AS f,batch b,facfeedback r where f.facid= '60,75,6' 
and f.Bthid=b.Bthid and r.bfid = f.bfid and b.class= 'PST'  
and year(b.examdate)= '2011' and month(b.examdate)= '10'

当我执行上述查询输出时如下

when i execute the above query output as follows

Rate    Bfid  Translated_Rate
1	72	100
2	72	75
3	72	50
4	72	25
1	74	100
2	74	75
3	74	50

来自上面的查询我希望输出如下;

from the above query i want the output as follows;

Rate   Bfid
 2.5    72
 2      74

我怎么做2.5意味着加上72 Bfid率1 + 2 + 3 + 4 = 10除以72'的分数。



否72'是4，所以10除以4是2.5。



来自上面的平均计算我怎样才能在sql server中使用我的上述查询。 br />


请帮帮我。



问候，

Narasiman P.

how i done 2.5 means add the 72 Bfid rate 1+2+3+4 = 10 divide by no of 72''s.

no of 72''s is 4, so 10 divide by 4 is 2.5.

from the above Average calculation how can i do using my above query in sql server.

please help me.

Regards,
Narasiman P.

推荐答案

我厌倦了将你的模式构建成一个示例数据库但是你有什么e得到的对我来说没有意义所以我尽了最大努力。



通过计算结果集，你会遇到更复杂的查询，所以你'有几个选择。您可以将数据转储到临时表（#tablename），表变量（@tablename），或者将select查询用作表本身（WITH TableName AS（SELECT * FROM TableName）。我选择使用with子句来完成它。

I tired to build your schema into a sample DB but what you''ve got just doesn''t make sense for me so i did my best.

By doing calculations off your result set, you are getting into more complex queries so you''ve got a few options. You could dump your data into a temp table (#tablename), table variable (@tablename), or use your select query as a table itself (WITH TableName AS (SELECT * FROM TableName). I chose to do it using the with clause.

 WITH ResultSet AS (
	SELECT DISTINCT 
		r.rate As Rate,
		f.bfid as BFid,
		CASE  
			WHEN r.rate = '1' THEN '100'
			WHEN r.rate = '2' THEN '75' 
			WHEN r.rate = '3' THEN '50'
			WHEN r.rate = '4' THEN '25' END AS Translated_Rate 
	FROM bthfac AS f,batch b,facfeedback r 
	WHERE f.facid= '60,75,6' 
		and f.Bthid=b.Bthid 
		and r.bfid = f.bfid 
		and b.class= 'PST' 
		and year(b.examdate)= '2011' 
		and month(b.examdate)= '10'
)

SELECT 
*,
	(SELECT SUM(CAST(Rate as float)) / COUNT(*) FROM ResultSet WHERE BFid = '72')
FROM ResultSet

这个应该工作。随着我能够用你的模式管理复制它...我换出表名/我不知道数据类型所以这就是为什么我说应该。因为我不知道你的数据库中是否有整数/浮点数或varchar的速率，所以CAST Rate as Float就在那里。

This "should" work. With what i was able to manage with your schema in replicating it...i swapped out table names/and i dont know data types so thats why i say "should". The CAST Rate as Float is there since i dont know if you have rate as an integer/float/or a varchar in your db.

SELECT AVG(Rate) as Rate, Bfid
FROM bthfac
GROUP BY bfid

汇率必须是浮点数或小数（18,2）

rate must be float or decimal(18,2)

这篇关于如何在Sql Server中使用以下查询找到Bfid值的平均值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！