MySQL Query Help (pretty please)

[Michael Wilding]

I'm trying to write a query that will pull out the amount of emails sent to each unique email address across the entire database, with a count of the amount of opens and clicks against it.

Can anyone help?

 

[twisted1919]

Something like this:

SELECT DISTINCT(ls.email), 
(SELECT COUNT(*) from mw_campaign_delivery_log WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_received, 
(SELECT COUNT(*) from mw_campaign_track_open WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_opened,
(SELECT COUNT(*) from mw_campaign_track_url WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_clicked
FROM mw_list_subscriber ls LIMIT 10

But be warned it is going to be super slow on large sets of data.

 

[Michael Wilding]

Something like this:

SELECT DISTINCT(ls.email),
(SELECT COUNT(*) from mw_campaign_delivery_log WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_received,
(SELECT COUNT(*) from mw_campaign_track_open WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_opened,
(SELECT COUNT(*) from mw_campaign_track_url WHERE subscriber_id IN (SELECT subscriber_id FROM mw_list_subscriber WHERE email = ls.email)) as emails_clicked
FROM mw_list_subscriber ls LIMIT 10

But be warned it is going to be super slow on large sets of data.

Brilliant, thank you.