Select total and Avg rating - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select total and Avg rating

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-07 : 01:08:23

Dear Friends,

I need to calculate the total and average rating for the particular content, i will explain with the table structure and the expected output, i hope i here i can get the experts..

I have 3 tables accordingly Kr_Reference, Kr_Referencehistory,kr_ReferenceRating.

Kr_Reference
------------

RefId CatId OwnerId
10 15 3
11 16 4
12 15 5

Kr_ReferenceHistory
--------------------

LinkId RefId Title Status Deleted
1 10 test1 0 0
2 10 test2 1 0
3 12 test5 1 0

Kr_ReferenceRating
-------------------
RatId RefId VoterId Points
1 10 100 2
2 10 101 4
3 10 102 5
4 12 33 3
5 12 44 1

From the Kr_Reference Table i have to show the results of category, where the CatId=15, so here i can filter 2 references 10 and 12

From the Kr_ReferenceHistory Table i have to check the 10 and 12 which is in active status(status=1), from here we have to fetch the 'title'. [Note : here more than one 10 and 12 refid may be found but only one will be active in the each reference id at a time, so we can find one title for refid = 10 and one title for refid=15.]

From the Kr_ReferenceRating Table we have to find how many times the refId 10 and 12 occured, at what are the 'total' point for the refid 10 and 12.

So My Expected output is

LinkId RefId Title NumberofVoter TotalRating AvgRating
2 10 test2 3 11 3.66
3 12 test5 2 4 2.00

Please help me to get my expected output, You may find my friends post also here for the same , i hope you can get the clear about my expected output..

Thanks in Advance friends...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:27:17

[code]SELECT rh.LinkId,r.RefId,rh.Title,
rr.TotalVoters,
rr.TotalRating,
rr.AvgRating
FROM Kr_Reference r
INNER JOIN Kr_ReferenceHistory rh
ON rh.RefId=r.RefId
AND rh.Status=1
INNER JOIN (SELECT RefId,SUM(Points)AS TotalRating,COUNT(VoterId) AS TotalVoters,SUM(Points)*1.0/COUNT(RatId) AS AvgRating
FROM Kr_ReferenceRating
GROUP BY RefId) rr
ON rr.RefId=r.RefId[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources