RESOLVED: Total Clicks per URL from Multiple emails

Use this forum for questions regarding adoption and functionality of OpenEMM

Moderator: moderator

timr
Posts: 33
Joined: Mon Aug 31, 2009 2:31 pm

RESOLVED: Total Clicks per URL from Multiple emails

Post by timr » Fri Aug 22, 2014 6:54 pm

Looks like I had the wrong column in the GROUP BY. When I GROUP BY full_url, it returns the counts I need.

select distinct(a.full_url), count(*) as num from rdir_log_tbl b
join rdir_url_tbl a on b.url_id=a.url_id where
b.mailing_id in (453,454) group by a.full_url order by a.full_url desc


I'm trying to get total clicks per URL for URL's used in multiple mailings.

Emails named A and B
URL named 1, 2 and 3

I'm trying to get the URL and total clicks per URL from URL used in emails A and B. I now this has to be pulled directly from the database but the query I'm using now returns total clicks for URL 1 in mailing A and total clicks for URL 1 in mailing B as 2 separate rows. I'm trying to get total clicks from mailing A and B as 1 row. This is the query I'm using now, which returns URL 1 Total clicks for Mailing A and URL 1 total clicks for mailing B.

select distinct a.full_url, count(*) as Num from rdir_log_tbl b
join rdir_url_tbl a on b.url_id=a.url_id where
b.mailing_id in (452,455) group by b.url_id order by Num desc

Thanks in advance