homepage Welcome to WebmasterWorld Guest from 54.196.195.158
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
How do I remove duplicate scans
Confused about where I should GROUP or DISTINCT
Wayder




msg:4644298
 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




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

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

Wayder




msg:4644336
 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




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

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

LifeinAsia




msg:4644338
 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




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

That sir, worked 100%.

I need to study it.

Thank you

arms




msg:4659973
 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved