Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql Syntax

Posted on 2008-10-06
1
Medium Priority
?
295 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:grogo21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22656301
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

Featured Post

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question