Welcome to WebmasterWorld Guest from 23.22.250.113

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)

10+ Year Member



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)

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



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

Wayder

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

10+ Year Member



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)

10+ Year Member



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

LifeinAsia

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

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



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)

10+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month