15,672,471 members
See more:
using query i get the below output as follows

Rate Bfid

1 72
2 72
3 72
4 72

From the above output i want the output as follows

Rate Bfid

2.5 72

adding the Rate values 1+2+3+4 = 10 divide bu number of 72's .

so in rate i get the 2.5

from using sql query how can i get the below output.

Rgds,
Narasiman P.
Posted

Solution 1

SQL
SELECT AVG(Rate), Bfid FROM tablename  GROUP BY Bfid

Edit: in case Rate is int, and not decimal, you will have to cast all values to decimal first, in order to be able to return decimal value. Like bellow:

SQL
CREATE TABLE #Test (Rate int, Bfid int)

INSERT INTO #Test (Rate, Bfid) VALUES (1, 72)
INSERT INTO #Test (Rate, Bfid) VALUES (2, 72)
INSERT INTO #Test (Rate, Bfid) VALUES (3, 72)
INSERT INTO #Test (Rate, Bfid) VALUES (4, 72)

SELECT AVG(CAST(Rate as decimal)), Bfid
FROM #Test
GROUP BY Bfid
ORDER BY Bfid

DROP TABLE #Test

v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 227 Graeme_Grant 65 Andre Oosthuizen 65 Dave Kreskowiak 62 Maciej Los 20
 OriginalGriff 1,654 Richard MacCutchan 506 Richard Deeming 290 Andre Oosthuizen 240 Graeme_Grant 233

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900