Sql Syntax

What is the actual data and expected results.  My current understanding is you want the sum for the whole entire list and likewise for count.  If that is true something like this would work.

WITH linkCTE AS (
	SELECT l.LinkID, l.NavigateURL, l.ImageFile, l.Status
	FROM LinkIndex l, UserRatings u, OutboundTraffic o
	Where l.LinkID = u.LinkID and l.LinkID = o.LinkID
	and l.Status In(0,1,2) and l.UserID = 1001 
)
SELECT DISTINCT *
,  (SELECT Sum(u.rating) FROM linkCTE) As Rating
,  (SELECT Count(o.LinkID) FROM linkCTE) As Clicks
FROM linkCTE

Select allOpen in new window

Hello,

I need to select LinkIndex.LinkID, LinkIndex.NavigateURL, LinkIndex.ImageFile, LinkIndex.Status.
 
I also need to select sum(UserRatings.Rating) of all records from UserRatings Table where UserRatings.LinkID = LinkIndex.LinkID(The Link id in the select statment above)
I also need to select count(*) of all records from OutboundTraffic Table where OutboundTraffic .LinkID = LinkIndex.LinkID(The Link id in the select statment above)

I tried your query and i got error:
The multi-part identifier "u.rating" could not be bound.

Thanks!

can u try this one will it work:
It can be done better my code, but i dont know wich table multiple your records so this way should work i think

SELECT
	l.LinkID,
	l.NavigateURL,
	l.ImageFile,
	l.Status,
	u.rating AS Rating,
	o.Clicks AS Clicks
FROM LinkIndex l
inner join ( 
		Select sum(rating) as Rating, LinkID
		from UserRatings 
		group by LinkID
	)
on l.LinkID = u.LinkID
inner join ( 
		Select count(LinkID) as Clicks, LinkID
		from OutboundTraffic
		group by LinkID
	) o
on l.LinkId = o.LinkId
							

Select allOpen in new window

i miss alias after first inner join

so correct syntax should be

	
SELECT
	l.LinkID,
	l.NavigateURL,
	l.ImageFile,
	l.Status,
	u.rating AS Rating,
	o.Clicks AS Clicks
FROM LinkIndex l
inner join ( 
		Select sum(rating) as Rating, LinkID
		from UserRatings 
		group by LinkID
	) u
on l.LinkID = u.LinkID
inner join ( 
		Select count(LinkID) as Clicks, LinkID
		from OutboundTraffic
		group by LinkID
	) o
on l.LinkId = o.LinkId
							

Select allOpen in new window

Cvijo123 your query looks good but there is one thing I still need.  When LinkId is not in OutboundLinks or UserRatings Table it does not return anything.  How can I select 0 for u.rating AS Rating or o.Clicks AS Clicks if they are not contained in the tables?

Thanks

YESS!!

Thanks so much!