Welcome to WebmasterWorld Guest from 107.20.54.98

Forum Moderators: open

Message Too Old, No Replies

How do I remove duplicate scans

Confused about where I should GROUP or DISTINCT

     
6:58 pm on Feb 11, 2014 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0


Hi,

I am trying to rerieve how many people have scanned our qr code today, but some people have scanned it twice and I am trying to remove scans that came from the same ip.

Columns are
id,qr_code_id, date_read, ip

I have

SELECT qr_code_id,COUNT(qr_code_id) AS qrcount, DATE(qr_date_read) bydate
FROM qr_promotions_log
WHERE DATE(qr_date_read) = CURDATE()
GROUP BY qr_code_id

Just to clarify, if a user from the same ip has scanned two different qr_code_id's then they should be counted, but if the user has scanned one qr_code_id twice then its a duplicate.

Can anyone help?

Thanks
9:01 pm on Feb 11, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5551
votes: 24


Add the IP field in both the SELECT and GROUP BY sections.
9:44 pm on Feb 11, 2014 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0


Thanks, I tried that without success. Maybe my explanation was not good enough.

The table qr_promotions_log has:
qr_code_id | qr_date_read | ip
3 | 2014-02-11 10:43:25 | 0.0.0.1
1 | 2014-02-11 10:43:30 | 0.0.0.1
1 | 2014-02-11 10:43:35 | 0.0.0.1
1 | 2014-02-11 10:50:55 | 0.0.0.2
1 | 2014-02-11 11:45:23 | 0.0.0.3

The result I am looking for is:
qr_code_id | qr_date_read | qrcount
3 | 2014-02-11 | 1
1 | 2014-02-11 | 3
9:48 pm on Feb 11, 2014 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0


I think I am looking for DISTINCT IP in a qr_code_id group
9:52 pm on Feb 11, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5551
votes: 24


Something like the following should work:
SELECT a.qr_code_id, a.bydate, COUNT(a.qrcount) AS qrcount
FROM (SELECT qr_code_id,COUNT(qr_code_id) AS qrcount, DATE(qr_date_read) bydate, ip
FROM qr_promotions_log
WHERE DATE(qr_date_read) = CURDATE()
GROUP BY qr_code_id, ip) a
GROUP BY a.qr_code_id, a.bydate
10:18 pm on Feb 11, 2014 (gmt 0)

Junior Member

10+ Year Member

joined:May 2, 2004
posts: 81
votes: 0


That sir, worked 100%.

I need to study it.

Thank you
4:16 pm on Apr 3, 2014 (gmt 0)

New User

joined:Feb 22, 2011
posts:22
votes: 0


SELECT qr_code_id, DATE(qr_date_read) bydate,COUNT(DISTINCT ip) AS qrcount
FROM qr_promotions_log
WHERE DATE(qr_date_read) = CURDATE()
GROUP BY qr_code_id, DATE(qr_date_read)

This would give you the result you need, simpler