CODEWITHSUNDEEP
phpmysqlsql
user3102920
user3102920

Reputation: 13

DISTINCT COUNT per day sql

I want to be able to COUNT DISTINCT IPs per day and then COUNT them all together.

Basically Monday I get 3 distinct IPs and out of those 3, two of them visited the page twice so I have a total of 5 records. On Tuesday I get 1 IP that was from Monday and 2 new distinct IPs. I want to be able to count 3 distinct IPs for Monday and 3 distinct IPs for tuesday and add them all together totaling 6 records. Here is my code:

SELECT *, COUNT(DISTINCT track.ip) AS tracks
FROM user, track
WHERE track.user_id = $user_id
GROUP BY $user_id
ORDER BY tracks
LIMIT 1

How can I COUNT my DISTINCT ips?

Upvotes: 1

Views: 2215

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Is this what you are looking for?

select sum(NumIpsPerDay)
from (select date(TheDayCol) as d, count(distinct t.ip) as NumIpsPerDay
      from track t
      group by date(TheDayCol) 
     ) t;

This calculates the number of distinct IPs on each day and then calculates the sum.

Upvotes: 3

Related Questions