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
[quoted text, click to view] > >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 ?
[quoted text, click to view] "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
> >
> >
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
[quoted text, click to view] 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
>
[quoted text, click to view] 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
--
Thx Guys, i found out..
Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DataGridItemEventArgs) Handles
DataGrid1.ItemDataBound
If ((e.Item.ItemType <> ListItemType.Header) And (e.Item.ItemType <>
ListItemType.Footer)) Then
Dim MyLabel As Label
MyLabel = CType(e.Item.FindControl("lblStarRating"), Label)
Dim i As Int32
i = Convert.ToInt32(e.Item.Cells(3).Text)
Dim j As Int32 = 0
MyLabel.Text = ""
If i <= 0 Then
MyLabel.Text = "Be the first to rate it!"
Else
For j = 1 To i
'MyLabel.Text += "*"
MyLabel.Text += "<IMG id='IMG1' src='stars.gif' >"
Next
End If
End If
very simple instead of "*" i have placed the html tag to include the image...
Thx a lot for you help
Don't see what you're looking for? Try a search.
