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

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

Syntax

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?

Thanks
0
grogo21
Asked:
grogo21
  • 2
1 Solution
 
tigin44Commented:
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
0
 
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?

Thanks
0
 
tigin44Commented:
just change the join type to left join.. thats all
0

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