15,667,864 members
See more: , +
My query as follows;

SQL
``` 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
```

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.

Regards,
Narasiman P.
Posted
Updated 20-May-13 2:32am
v4

## Solution 1

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.

SQL
``` 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```

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.

v2

## Solution 2

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

rate must be float or decimal(18,2)

v3