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
Aucun commentaire:
Enregistrer un commentaire