I'm trying to pull out visits / unique visits / clicks / unique clicks
in 1 query.
Code:
SELECT dbo.m_referrals.referral_title, dbo.m_referrals.referral_id, COUNT(dbo.m_tracking.log_id) AS Visits,
COUNT(DISTINCT dbo.m_tracking.tracking_session_id) AS UniqueVisits
FROM dbo.m_tracking INNER JOIN
dbo.m_referral_detail ON dbo.m_tracking.tracking_referral_detail_id = dbo.m_referral_detail.referral_detail_id INNER JOIN
dbo.m_referrals ON dbo.m_referral_detail.referral_id = dbo.m_referrals.referral_id
GROUP BY dbo.m_referrals.referral_title, dbo.m_referrals.referral_id
This works fine for visits and unique visits. To work out clicks i have tried doing a subquery to pull out where object_id <> 0 so i know a user has clicked out but I get an error saying that i do not have object_id in my GROUP BY clause. If i had object_id to the GROUP BY clause the stats are repeated. Is there a way round this or am I asking too much?
I hope i've explained this well enough, its very difficult to explain what i'm trying to do!
Matt