Re: How to Join a table with Other (result) Tables ? Complex one

Hey thx a lot, That worked..

to add one more count as RatingCount. i added "(COUNT(*)) AS RatingCount"

select
D.docID, D.docName, COALESCE(AVG(rating),0) AS avgRating, (COUNT(*)) AS
RatingCount
from tbl_documents as D
left outer join tbl_Rating as R
on R.docID = D.docID
group by D.docID, D.docName

the result i get it like this

> >docID docName Avaragerating RatingCount
> >1 aaa 3 3
> >2 bbb 3 1
> >3 ccc 0 1

But Actully the Rating that must show 0 in third Row

what should i do ?

"Steve Kass" wrote:

> Try something like this:
>
> select
> D.docID, D.docName, COALESCE(AVG(rating),0) AS avgRating
> from tbl_documents as D
> left outer join tbl_Rating as R
> on R.docID = D.docID
> group by D.docID, D.docName
>
> If you need the average to show 3.333333 instead of 3, use this
> expression for avgRating: COALESCE(AVG(1.0*rating),0.0)
>
> Steve Kass
> Drew University
>
> velu wrote:
>
> >Here is the situation
> >
> >Table 1 : tbl_documents
> >
> >docID docName
> >1 aaa
> >2 bbb
> >3 ccc
> >
> >Table 2 : tbl_Rating
> >
> >ratID rating docID
> >1 3 1
> >2 5 1
> >3 2 1
> >4 3 2
> >
> >The queary I need is to display the result in this form. must be like this
> >
> >docID docName Avaragerating
> >1 aaa 3
> >2 bbb 3
> >3 ccc 0
> >
> >NOTE : 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
> >
> >
>
.

Relevant Pages