SQL multiple counts from multiple tables

Question

I have the following tables (“All_Countries” & “_Sent”) and need to get the result as presented in the “Result” table. What I want is to count all “SubscriberKeys” and the total “SendIDs” connected to these SubscriberKeys, grouped by “Source” – just as in the “Result” table. I managed to achieve this (I think) by using the query below but I’m not sure if I did it the wrong way. Isn’t there a better (and more efficient) way of doing this with just one select statement and without the extra sub query? I use SQL server 2005.

All_Countries

-------------------------------
SubscriberKey*    | Source
-------------------------------
10001             | Campaign1
10002             | Campaign2
10003             | Campaign1

_Sent

-----------------------
SendID*| SubscriberKey*
-----------------------
1      | 10001
2      | 10001
3      | 10002
4      | 10002
5      | 10003
6      | 10003

Result

-----------------------------------------------------
Source*          | SubscriberCount       | SendCount
-----------------------------------------------------
Campaign1        | 2                     | 4
Campaign2        | 1                     | 2

Primary keys = * (e.g where you have a star in the column)

SELECT a.Source, COUNT(a.SubscriberKey) AS Subscribers, 
(SELECT COUNT(b.SubscriberKey) AS Sent FROM _Sent AS b
INNER JOIN All_Countries AS c ON b.SubscriberKey = c.SubscriberKey
WHERE c.Source = a.Source) AS Sent
FROM All_Countries AS a
GROUP BY a.Source

use ctrl-K to format as code, not need insert html to format the spaces. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 16, 2016 at 14:11
Thanks for the tip @JuanCarlosOropeza! I'm a little bit of a newbie here :) — Simon G
– Simon G, Commented Jun 17, 2016 at 12:31

Rhys Jones · Accepted Answer · 2016-06-16 14:08:23Z

1

I haven't tested this but I think you can get what you want by using the DISTINCT keyword inside your first COUNT

SELECT
    c.Source,
    COUNT(DISTINCT SubscriberKey) AS SubscriberCount,
    COUNT(*) AS SendCount
FROM
    All_Countries c
        JOIN _Sent s ON s.SubscriberKey = c.SubscriberKey 
GROUP BY
    c.Source

edited Jun 16, 2016 at 14:08

answered Jun 16, 2016 at 14:04

Rhys Jones

5,5361 gold badge25 silver badges44 bronze badges

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

Comments

gofr1 · Accepted Answer · 2016-06-16 14:10:57Z

0

SELECT  [Source],
        COUNT(DISTINCT a.SubscriberKey) as SubscriberCount,
        COUNT(SendId) as SendCount
FROM All_Countries a
INNER JOIN _Sent s 
    ON s.SubscriberKey = a.SubscriberKey
GROUP BY [Source]

Output:

Source      SubscriberCount SendCount
Campaign1   2               4
Campaign2   1               2

answered Jun 16, 2016 at 14:10

gofr1

16k11 gold badges47 silver badges56 bronze badges

Comments

Matias Thayer · Accepted Answer · 2016-06-16 14:06:11Z

0

how about a query like this?

select 
    source, count(distinct a.SubscriberKey) as SubscriberCount, count(distinct b.SendID) as SendCount
from All_Countries a join 
     _Sent b ON a.SubscriberKey = b.SubscriberKey

answered Jun 16, 2016 at 14:06

Matias Thayer

6015 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL multiple counts from multiple tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related