SQL Count between two tables

Question

I currently have two separate queries in SQL Server that count the number of times a table contains a unique ID in a week. I would like to display these using one query, not two.

This data is held in two separate views, hence my writing two queries. These are ActivityPointer and Asp_dealercallreport.

Query #1:

SELECT 
    OwnerIDName, 
    COUNT(Distinct ActivityID) AS CalendarEvents
FROM 
    ActivityPointer
WHERE
    /*Specify Activity code for Calendar Events*/
    ActivityTypeCode = '4201'
    /*Specify Calendar Events from this week only*/
    AND ScheduledStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    AND ScheduledStart <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
    /*Specify users to be reported on by Name*/
    AND OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY 
    OwnerIDName

Query #2:

SELECT 
    OwnerIDName, 
    COUNT(Distinct Asp_dealercallreportId) AS DealerVisits
FROM 
    Asp_dealercallreport
    /*Specify Calendar Events from this week only*/
WHERE    
    asp_callreportdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    AND asp_callreportdate <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
    /*Specify  to be reported on by Name*/
    AND OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY 
    OwnerIDName

Thanks

Usually, you can use some of the system tables, which contains only one row or create a temporary one, and then solve both queries as a subquery, so you will have one row, and two fields on the same query. — facundofarias
– facundofarias, Commented Jan 18, 2015 at 20:05

Dmitriy Olhovsky · Accepted Answer · 2015-01-18 20:12:23Z

1

Maybe you can simply use INNER JOIN operator? Like this:

SELECT 
    ap.OwnerIDName, 
    COUNT(Distinct ap.ActivityID) AS CalendarEvents, 
    COUNT(Distinct a_dcr.Asp_dealercallreportId) AS DealerVisits
FROM 
    ActivityPointer ap
INNER JOIN 
    Asp_dealercallreport a_dcr ON ap.OwnerIDName=a_dcr.OwnerIDName
WHERE
/*Specify Activity code for Calendar Events*/
    ap.ActivityTypeCode = '4201'
/*Specify Calendar Events from this week only*/
    AND ap.ScheduledStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    AND ap.ScheduledStart <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
/*Specify Calendar Events from this week only*/
    AND a_dcr.asp_callreportdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    AND a_dcr.asp_callreportdate <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
/*Specify users to be reported on by Name*/
    AND ap.OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY 
    ap.OwnerIDName

answered Jan 18, 2015 at 20:12

Dmitriy Olhovsky

1411 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JimmieMaul Over a year ago

Wizardry. Thank you. I need to brush up on my JOINs. When I was doing this I was coming up with some strange results.

Gordon Linoff · Accepted Answer · 2015-01-18 20:06:46Z

1

Use conditional aggregation:

SELECT OwnerIDName,
       COUNT(Distinct case when ScheduledStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0) AND
                                ScheduledStart <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
                           then ActivityID end) AS CalendarEvents_Scheduled,
       COUNT(Distinct case when asp_callreportdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0) and
                                asp_callreportdate <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
                           then ActivityID end) AS CalendarEvents_asp
FROM ActivityPointer
WHERE ActivityTypeCode = '4201' AND
      OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY OwnerIDName;

answered Jan 18, 2015 at 20:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Nikhil Girraj Over a year ago

Aren't you missing the point that there are actually two tables used in the question?

syseng-km · Accepted Answer · 2015-01-18 20:26:57Z

Using a standard SQL UNION will provide you with the output of both queries in one result set. As long as they have same number and type of columns:

http://www.w3schools.com/sql/sql_union.asp

SELECT OwnerIDName, COUNT(Distinct ActivityID) AS CalendarEvents
FROM ActivityPointer
WHERE
/*Specify Activity code for Calendar Events*/
    ActivityTypeCode = '4201'
/*Specify Calendar Events from this week only*/
AND ScheduledStart >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND ScheduledStart <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
/*Specify users to be reported on by Name*/
AND OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY OwnerIDName    
UNION
SELECT OwnerIDName, COUNT(Distinct Asp_dealercallreportId) AS DealerVisits
FROM Asp_dealercallreport
/*Specify Calendar Events from this week only*/
WHERE    asp_callreportdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7  * 7, 0)
AND    asp_callreportdate <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)
/*Specify  to be reported on by Name*/
AND    OwnerIdName IN ('John Doe', 'Jane Doe')
GROUP BY OwnerIDName

Collectives™ on Stack Overflow

SQL Count between two tables

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related