CODEWITHSUNDEEP
mysql
cyberfly
cyberfly

Reputation: 5868

MYSQL COUNT WITH 3 DISTINCT

Hi I have this VISITS table

enter image description here

What I want to achieve:

**affiliate_id** **unique visits count** 

167               4
121               1
137               1

Special Condition is one IP can only be counted once per day for single affiliate_id.

So for visit_id 553 and 554, it can be only counted as one visits because both have same ip, same date and same affiliate_id.

From what I understand I need to group by ip, date and affiliate_id and count it, but not sure how to write the query.

Can you guys point me to some reference or insight to solve this problem?

Thanks in advance!

--

Update with link sample SQL:

https://dl.dropboxusercontent.com/u/3765168/tb_visits.sql

Upvotes: 0

Views: 47

Answers (3)

Dario
Dario

Reputation: 2723

If I understood correctly,

SELECT affiliate_id, count(*) 
  FROM (SELECT DISTINCT affiliate_id, ip, DAY(date)
          FROM visits) AS q
  GROUP BY affiliate_id;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

Based on your requirement i think you need the distinct ip per date and affiliate_id

 select DATE(date), affiliate_id, count(distinct( ip))
 from your_table
 group by DATE(date), affiliate_id

Upvotes: 1

Dave Goten
Dave Goten

Reputation: 701

What you are trying to do is group the number of unique or distinct ip's for a given affiliate_id so the only group by you need is the affiliate_id. The Unique hits are calculated using a count and to make then unique you add the DISTINCT key word

SELECT 
    affiliate_id, COUNT(DISTINCT ip) AS unique_visit_counts, 
FROM tablename
GROUP BY affiliate_id

However since you want it by the day as well you might want to include a date clause such as:

DATE_FORMAT(date, "%y-%m-%d") AS `date`

Which will turn your date and time stamp into a day in the YY-MM-DD format.

If you group by that you can get a full list by day by affiliate_id using something like

SELECT 
    affiliate_id, 
    COUNT(DISTINCT ip) AS unique_visit_counts, 
    DATE_FORMAT(date, "%y-%m-%d") AS `date`  
FROM tablename
GROUP BY `date`, affiliate_id

Or pick a specific date using something like

SELECT 
    affiliate_id, 
    COUNT(DISTINCT ip) AS unique_visit_counts, 
FROM tablename
WHERE DATE_FORMAT(date, "%y-%m-%d") = '17-02-08'
GROUP BY affiliate_id

Upvotes: 0

Related Questions