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

On Thu, 25 Aug 2005 04:36:01 -0700, velu wrote:

>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 ?

Hi velu,

Replace
(COUNT(*)) AS RatingCount
with
COUNT(rating) AS RatingCount

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.