[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:


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?

  • 2
1 Solution
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 a.date = b.date
grogo21Author Commented:
I had to make a few changes to make it work:

select a.date, 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 a.date = b.date

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?

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now