Posted on 2008-11-07
Last Modified: 2012-05-05
Hello, I have the following tables:
Outbound: OutboundID, VisitDate
Inbound: InboundID, ClickDate

How can I select count(OutboundID) and count(InboundID) and group by VisitDate?

Question by:grogo21
    LVL 26

    Expert Comment

    select date, sumOutbound, sumInbound
    from (select convert(datetime, OutboundID, 104) date, count(*) sumOutbound from Outbound group by convert(datetime, OutboundID, 104) a
    inner join
    (select convert(datetime, InboundID, 104) date, count(*) sumInbound from Inbound group by convert(datetime, InboundID, 104) b on =

    Author Comment

    I had to make a few changes to make it work:

    select, sumOutbound, sumInbound
    from (select convert(VARCHAR(10), ClickDate, 120) date, count(*) sumOutbound from Outbound group by convert(VARCHAR(10), ClickDate, 120)) a
    inner join
    (select convert(VARCHAR(10), VisitDate, 120) date, count(*) sumInbound from Inbound group by convert(VARCHAR(10), VisitDate, 120)) b on =

    However, no records are returned by the query.  The first select from Outbound returns the following records:
    2008-10-29      4
    2008-10-30      15
    2008-11-02      7
    2008-11-03      5

    The inbound table does not currently have any records.  How can I still make the query you wrote display the records returned from Outbound table and coalesce the inbound count with zero?

    LVL 26

    Accepted Solution

    just change the join type to left join.. thats all

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now