Sql Syntax

Hello, I need to alter this portion:

                Select count(LinkID) as Clicks, LinkID
                from OutboundTraffic
                group by LinkID

of the sql query i have below so that I can do a count of all records with the correct LinkID (as it does now) and also get a count of all records with the correct LinkID with a OutboundTraffic.ClickDate value = to todays date.  Im not sure of the syntax required to make this work.

Thanks
SELECT
        l.LinkID,
        l.NavigateURL,
        l.ImageFile,
        l.Status,
        isnull(l.StartDate, GetDate()) As StartDate,
        isnull(u.rating,0) AS Rating,
        isnull(o.Clicks,0) AS Clicks
FROM LinkIndex l
left join ( 
                Select sum(rating) as Rating, LinkID
                from UserRatings 
                group by LinkID
        ) u
on l.LinkID = u.LinkID
left join ( 
                Select count(LinkID) as Clicks, LinkID
                from OutboundTraffic
                group by LinkID
        ) o
on l.LinkId = o.LinkId
where status in (0,1,2) and userid = 1001
order by startdate

Open in new window

grogo21Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You can modify like this:
Select count(LinkID) as Clicks, LinkID
                , SUM(Case DateDiff(dd, ClickDate, getdate()) When 0 Then 1 Else 0 End) AS TodaysClicks
                from OutboundTraffic
                group by LinkID

Using a case when to generate a 1 or 0 for records matching criteria and then use SUM to simulate a count of the 1's should do the trick.

SELECT
        l.LinkID,
        l.NavigateURL,
        l.ImageFile,
        l.Status,
        isnull(l.StartDate, GetDate()) As StartDate,
        isnull(u.rating,0) AS Rating,
        isnull(o.Clicks,0) AS Clicks,
        isnull(o.TodaysClicks,0) AS TodaysClicks
FROM LinkIndex l
left join ( 
                Select sum(rating) as Rating, LinkID
                from UserRatings 
                group by LinkID
        ) u
on l.LinkID = u.LinkID
left join ( 
                Select count(LinkID) as Clicks, LinkID
                , SUM(Case DateDiff(dd, ClickDate, getdate()) When 0 Then 1 Else 0 End) AS TodaysClicks
                from OutboundTraffic
                group by LinkID
        ) o
on l.LinkId = o.LinkId
where status in (0,1,2) and userid = 1001
order by startdate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.