MYSQL COUNT WITH 3 DISTINCT

Question

Hi I have this VISITS table

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

See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2017-02-09 16:47:55 +00:00
Commented Feb 9, 2017 at 16:47 — Strawberry
– Strawberry, Commented Feb 9, 2017 at 16:47

ScaisEdge · Accepted Answer · 2017-02-09 17:36:48Z

1

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

edited Feb 9, 2017 at 17:36

answered Feb 9, 2017 at 16:47

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cyberfly Over a year ago

Thanks but the query not grouping by affiliate_id as in picture below dl.dropboxusercontent.com/u/3765168/query1.png

cyberfly Over a year ago

Hi @Dario answer is the most correct, however your answer also give me something that I need, so I am giving you vote for your assistant. Thanks man!

Dario · Accepted Answer · 2017-02-09 17:43:01Z

0

If I understood correctly,

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

edited Feb 9, 2017 at 17:43

answered Feb 9, 2017 at 16:51

Dario

2,72322 silver badges24 bronze badges

3 Comments

cyberfly Over a year ago

Thanks but the query group all visits into one affiliate_id only as in picture below dl.dropboxusercontent.com/u/3765168/query2.png

Dario Over a year ago

Sorry I had been too hasty. Edited.

cyberfly Over a year ago

Thanks sir your query is exactly what I need after scrathing my head for a day before coming to StackOverflow :)

Dave Goten · Accepted Answer · 2017-02-09 17:08:17Z

0

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

edited Feb 9, 2017 at 17:08

answered Feb 9, 2017 at 16:59

Dave Goten

7014 silver badges13 bronze badges

4 Comments

cyberfly Over a year ago

Hi Dave thanks for your insight, I did try your query but it still not grouping by affiliate_id as in picture below dl.dropboxusercontent.com/u/3765168/notgroupingbyid.png I upload sample SQL with data for reference dl.dropboxusercontent.com/u/3765168/tb_visits.sql

Dave Goten Over a year ago

Is there a reason why IP needs to be tinytext? can it be varchar(255) or better yet varchar(40). It seems like a waste if you're only storing an IP in there. Anyway DISTINCT doesn't work with text type fields in SQL so you'll have to either change it (preferred) or CAST it CAST(ip as varchar(255)) Haven't tested the code yet but something like ... COUNT(DISTINCT CAST(ip as varchar(255))) Might work?

cyberfly Over a year ago

Hi the IP tinytext is out of my control since it was a table from third party library / extension. I have accepted the other answer but your help is really appreciated. Thanks again sir for your kind soul.

Dave Goten Over a year ago

np Best of luck to you. I'm glad that you found your answer but I will express a bit of my concern with the use of the DAY() function if you plan on having data that spans over a month. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofmonth Might cause some unexpected results, unless that's what you're going for. In which case I'm way in left field. DATE() maybe more what you're looking for with that answer.

Collectives™ on Stack Overflow

MYSQL COUNT WITH 3 DISTINCT

3 Answers 3

2 Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related