CODEWITHSUNDEEP
mysqlsqlgroup-by
Grogu
Grogu

Reputation: 2475

How to select query count only once duplicate rows with MYSQL GROUP BY?

I'm building a very light weight web analytics software. The user select date range and can view how many unique and returning visitors. I'm trying to display unique visitors with GROUP BY. Problem with GROUP BY is that it does not remove duplicates. A visitor should only be counted once even if he visited twice on the same day. How to do this?

What I have so far

SELECT last_update, COUNT(ipAddress) as uniqueVisitor 
FROM geolocation 
WHERE last_update 
BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY date(last_update)

This returns

last_update          | uniqueVisitor
2020-11-11 19:29:45  | 3
2020-11-13 19:53:16  | 5

Each ipAddress should only be counted once per day.

Desired result

last_update          | uniqueVisitor
2020-11-11 19:29:45  | 2
2020-11-13 19:53:16  | 3

Upvotes: 0

Views: 855

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Use count(distinct):

SELECT last_update, COUNT(DISTINCT ipAddress) as uniqueVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY date(last_update);

I would recommend not using just last_update in the SELECT -- the query should generate an error. Instead:

date(last_update)
min(last_update)
max(last_update)

Upvotes: 1

Related Questions