Complex query - 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

Complex query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 05:06:09

Hi Friends,

I have three tables i need to calculate the Total rating, average rating, and number of votes, and i want to display the each voters individual vote.

Table Name : reference

RefId CatId UserId Status Deleted
2 12 17 1 0
2 25 4 1 0
4 25 4 1 0

Table Name : ReferenceHistory

LinkId refId UserId Title URL Description abusive status deleted catId

1 3 5 test1 test1URL testdesc1 0 0 0 12
2 4 6 test2 test2URL testdesc2 0 1 0 25
3 4 7 test3 test3URL testdesc3 0 0 0 25

TableName ReferenceRating

RatId RefId UserId Point
1 2 25 0
2 3 12 0
3 4 4 3
4 4 25 3
5 4 3 3

If the table have status, deleted,abusive , i need to check with status=1, deleted=0 and abusive=0 in applicable tables.

From these tables i need to calculate total rating for each and every reference with users individual rating for the reference average rating for the reference and the total number of voters..
The total number of voters need to be calculated from the referencerating and if the user rating is zero that user should not be counted in the number of voters.

I have the Query , i need all your help to modify the query to get my expected results..

SELECT Top 2 rh.LinkId,r.RefId,r.CatId,rh.Title,rh.URL,rh.Description,rr.TotalVoters,rr.Points,rr.avgrating,rr.Rating FROM Kr_Reference r INNER JOIN Kr_ReferenceHistory rh ON rh.RefId=r.RefId AND rh.Status=1 AND rh.Abusive=0 AND r.Status=1 AND r.catid=25 INNER JOIN (SELECT RefId,SUM(Points)AS Points,COUNT(UserId) AS TotalVoters,SUM(Points)/COUNT(RatId) AS Rating,SUM(Points)*1.0/COUNT(RatId) AS avgrating FROM Kr_ReferenceRating GROUP BY RefId) rr ON rr.RefId=r.RefId

But here in the reference rating table i have the status , i need to calculate the total number of voters when the status=0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:16:44

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114452

E 12°55'05.63"
N 56°04'39.26"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 05:29:07

Peso,

I need the solution for that, Could you please help me to get my expected result.

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources