Rating and Average 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

Rating and Average Rating

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 01:44:21

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 need the expected output with two difference scenario

1. In the where condition i will pass CatId only, consider where cat id = 25

refId, LinkId,catId, Title, URL , Description, TotalRating, AverageRating, Number of Votes

3 1 25 test1 test1URL test1Desc 0 0 0
4 2 25 test2 test2URL test2Desc 9 3 3

2. I need to pass CatId and UserId.

CatId=25, UserId=25

If the user voted for the particular reference i need to show the individual vote, if they are not voted for the reference but that are in the cat=25 means, there i need to show the rating of the individual user is 0..

refId, LinkId,catId, userId Title, URL , Description,IndividualVote, TotalRating, AverageRating, Number of Votes

3 1 25 12 test1 test1URL test1Desc 0 0 0 0
4 2 25 25 test2 test2URL test2Desc 3 9 3 3

Note :
For RefId 4 the user 25 voted 3 points so the individual Vote is 3
For refId 3 the user 25 did not voted so the individual vote is 0

Thanks in Advance friends..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 02:38:34

can i ask how you got refid 3 among results when you pass catid 25? i cant see a record for refid 3 with catid 25

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 03:54:55

Hi Vissakh,

Thank you for your reply,

I made a mistake in the reference table ,

Please consider this as a sample data for REFERENCE table

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

Thank in Advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 04:36:49

Hello Friends,

Please help me to get the expected result for the above request..

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 06:57:00

quote:
Originally posted by dhinasql

Hi Vissakh,

Thank you for your reply,

I made a mistake in the reference table ,

Please consider this as a sample data for REFERENCE table

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

Thank in Advance

even then your reference history table doesnt have any record for refid 3 with catid 25. is that also posted wrong?

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 07:15:17

Yes visakh,

Hope i do understand my expected result, could you please help me to sort out this please.

THanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:21:31

first post proper data sample with reqd output please

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 08:32:05

Vishak as per you said i am posting the data AGAIN, Please help me
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
3 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 25
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 25 0
3 4 4 3
4 4 25 3
5 4 25 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 need the expected output with two difference scenario

1. In the where condition i will pass CatId only, consider where cat id = 25

refId, LinkId,catId, Title, URL , Description, TotalRating, AverageRating, Number of Votes

3 1 25 test1 test1URL test1Desc 0 0 0
4 2 25 test2 test2URL test2Desc 9 3 3

2. I need to pass CatId and UserId.

CatId=25, UserId=25

If the user voted for the particular reference i need to show the individual vote, if they are not voted for the reference but that are in the cat=25 means, there i need to show the rating of the individual user is 0..

refId, LinkId,catId, userId Title, URL , Description,IndividualVote, TotalRating, AverageRating, Number of Votes

3 1 25 12 test1 test1URL test1Desc 0 0 0 0
4 2 25 25 test2 test2URL test2Desc 3 9 3 3

Note :
For RefId 4 the user 25 voted 3 points so the individual Vote is 3
For refId 3 the user 25 did not voted so the individual vote is 0

Thanks in Advance friends..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 08:53:08

SELECT r.refId, rh.LinkId,r.catId, rh.Title, rh.URL , rh.Description, rr.TotalRating, rr.AverageRating, rr.[Number of Votes]
FROM reference r
INNER JOIN ReferenceHistory rh
ON rh.RefId=r.RefId
AND rh.UserId=r.UserId
INNER JOIN (SELECT RefId,SUM(CASE WHEN Point=0 THEN 0 ELSE 1 END)AS [Number of Votes],SUM(Point) AS TotalRating,AVG(CASE WHEN Point=0 THEN Point ELSE NULL END) AS AverageRating
            FROM ReferenceRating 
            GROUP BY RefId)rr
ON rr.RefId=r.RefId
WHERE r.CatID=@CatID

@CatID is passed on catid value

2.

SELECT r.refId, rh.LinkId,r.catId, rh.Title, rh.URL , rh.Description, COALESCE(rr.TotalRating,0), COALESCE(rr.AverageRating,0), COALESCE(rr.[Number of Votes],0)
FROM reference r
INNER JOIN ReferenceHistory rh
ON rh.RefId=r.RefId
AND rh.UserId=r.UserId
LEFT JOIN (SELECT RefId,UserID,SUM(CASE WHEN Point=0 THEN 0 ELSE 1 END)AS [Number of Votes],SUM(Point) AS TotalRating,AVG(CASE WHEN Point=0 THEN Point ELSE NULL END) AS AverageRating
            FROM ReferenceRating 
            GROUP BY RefId,UserId)rr
ON rr.RefId=r.RefId
AND rr.UserId=r.UserId
WHERE r.CatID=@CatID
AND r.UserID=@UserID

i still believe your sample data is not proper as i find userid on both reference and reference history tables but their userid does not correspond. if you're concerned only on refid remove condition in red above.

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 09:25:54

visakh16 thank you so much for your time,

for the last three column i am getting no column name,

refId, LinkId,catId, Title ,URl, Description, No Column name , no column name, no column name

But i Need the column name , Please help to get the Column name

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 09:38:02

Vishak,

I used alaise name so i got the Name for the column..

Thanks for your time

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-14 : 09:44:18

Vishak,

Thank you so much for Your help,

I need to display the reference details if the user rated for the particular reference , i have to show the individual point of the user, if he is not voted for the particular reference , we have to show the individual point as 0.

Please help me i need the individual vote when we pass the userid

Expected format
refId, LinkId,catId, userId Title, URL , Description,IndividualVote, TotalRating, AverageRating, Number of Votes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 10:56:07

quote:
Originally posted by dhinasql

Vishak,

Thank you so much for Your help,

I need to display the reference details if the user rated for the particular reference , i have to show the individual point of the user, if he is not voted for the particular reference , we have to show the individual point as 0.

Please help me i need the individual vote when we pass the userid

Expected format
refId, LinkId,catId, userId Title, URL , Description,IndividualVote, TotalRating, AverageRating, Number of Votes

what do you mean by individual vote? all the votes made by user for reference individually as records rather than total count?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources