August 25, 2005 at 4:18 am
Here is the situation Table 1 : tbl_documentsdocIDdocName1aaa2bbb3cccTable 2 : tbl_RatingratIDratingdocID131251321432The queary I need is to display the result in this form. must be like thisdocIDdocName Avaragerating1aaa32bbb33ccc0NOTE : For getting the average I used this queary “SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID = tbl_documents.docID” PLs help me ?Thx
August 25, 2005 at 5:26 am
Try this
select a.docid, a.docname, isnull(b.avaragerating,0)avaragerating
from tbl_documents a
left outer join
(select docid, sum(rating)/count(*)as avaragerating
from tbl_rating
group by docid) b on a.docid = b.docid
August 25, 2005 at 6:00 am
Thx that Worked !
if i want to add a another Column called as RatingCount what should i Do ? like i need like this
docIDdocName Avaragerating Rating Count
>1aaa3 2 (no of users rated)
>2bbb3 1
>3ccc0 0
August 25, 2005 at 9:08 pm
select a.docid, a.docname, isnull(b.avaragerating,0)avaragerating,
isnull(b.ratingcount,0) as ratingcount
from tbl_documents a
left outer join
(select docid, sum(rating)/count(*)as avaragerating, count(*) as ratingcount
from tbl_rating
group by docid) b on a.docid = b.docid
August 25, 2005 at 11:20 pm
thx it worked..thx a lot..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply