|
Home > Archive > MS SQL Server MSEQ > August 2005 > Re: How to Join a table with Other (result) Tables ? Complex one
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: How to Join a table with Other (result) Tables ? Complex one
|
|
| Steve Kass 2005-08-25, 7:24 am |
| 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
>
>
| |
|
| 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
But Actully the Rating that must show 0 in third Row
what should i do ?
"Steve Kass" wrote:
[color=darkred]
> 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:
>
>
| |
| Hugo Kornelis 2005-08-25, 8:24 pm |
| 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
>
>
>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)
| |
|
|
|
| Thx Guys, i found out..
Private Sub DataGrid1_ItemDataBo
und(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls. DataGridItemEventArg
s) 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
|
|
|
|
|