jeudi 16 juin 2016

SQL multiple counts from multiple tables

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