Counting duplicates with within three column tuple

I have a table with the format shown below (Exhibit 1). The table has 38mm records in it representing Opens and Clicks for three months. A separate report has shown that there were 11mm opens in the month of January, of which 7mm were unique. I would like to verify that this table has 7mm unique opens. OpenFlag has a 1 if a Subscriber opened an Email, and a 0 if not. When there are multiple messages with unique MessageID’s per subscriber, I only want to count 1 (dedup).

Could anybody point me in the direction of a relatively simple query (using Count, Sum, distinct etc) that could count unique opens? I know I can use @vars to distinguish the first subscriberID/month within a set of equal subscriberID/months but with different messageID’s and keep running counts, but I’d like to avoid that complexity in this case.

Exhibit 1:

create table TrackerSub
(  Id int(11) NOT NULL AUTO_INCREMENT,
  Time dateti开发者_如何学运维me NOT NULL,
  SubscriberId int(11) DEFAULT NULL,
  MessageId int(11) Default NULL,
  OpenFlag int(1) default null,
  ClickFlag int(1) default null,
  Month int(2) default null,
  PRIMARY KEY  (`Id`)
);

---

if you're just testing to see that a subscribe has opened any email from the database:

SELECT SUM(SubOpens) FROM (
    SELECT 1 SubOpens FROM TrackerSub
    WHERE OpenFlag = 1
    GROUP BY SubscriberID
) opens

if you're trying to see how many unique subscribers opened each message...

SELECT MessageId, SUM(SubOpens) FROM (
    SELECT MessageId, 1 SubOpens FROM TrackerSub
    WHERE OpenFlag = 1
    GROUP BY MessageId, SubscriberId
) opens
GROUP BY MessageId

I think that accurately represents your schema (at least, how I understood it) - and helps! Good luck!