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
grogo21Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
just change the join type to left join.. thats all
0
 
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
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.