How do I remove duplicate scans

Forum Moderators: open

Message Too Old, No Replies

How do I remove duplicate scans

Confused about where I should GROUP or DISTINCT

Wayder

6:58 pm on Feb 11, 2014 (gmt 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

LifeinAsia

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

Add the IP field in both the SELECT and GROUP BY sections.

Wayder

9:44 pm on Feb 11, 2014 (gmt 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

Wayder

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

I think I am looking for DISTINCT IP in a qr_code_id group

LifeinAsia

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

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

Wayder

10:18 pm on Feb 11, 2014 (gmt 0)

That sir, worked 100%.

I need to study it.

Thank you

arms

4:16 pm on Apr 3, 2014 (gmt 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