Building a MYSQL query based on multiple unique listings

Question

Sorry for the crappy title - didn't know how else to say it.

I have a commenting database where anyone can report a comment for abuse (anyone as in not just logged in members). This obviously means a person could report a comment multiple times. Where I need help is building a query that shows the number of times a comment has been reported but by unique email address AND ip address.

I'm not sure if that is clear or not -- if [email protected] of IP address 1.2.3.4 reports a message more than once, it will only count as one report. If there is an additional record of [email protected] with an IP of 4.5.6.7 reporting the same comment, it would count as two reports since the IPs are different.

The fields, all in one table:

• id
• comment_id
• note
• email
• ip_address

I hope that all makes sense and any help would be GREATLY appreciated!

Answer 1

SELECT  email, ip_address, COUNT(DISTINCT note)
FROM    log
GROUP BY
        email, ip_address

Update:

As per your comment, I believe you want this:

SELECT  comment_id, COUNT(DISTINCT email, ip_address)
FROM    log
GROUP BY
        comment_id

Answer 2

SELECT 
     comment_id,
     COUNT(DISTINCT CONCAT(email,'|',ip_address))
FROM  log
GROUP BY comment_id

As OMG Ponies said, adding a unique in email+ip_address would help, possibly with this to prevent lost notes:

INSERT INTO log (...) VALUES (...) ON DUPLICATE KEY note = CONCAT(note,' ',VALUES(note));