How to filter data from SQL that appears only once? [duplicate]

Question

Here is an example of my SQL structure

As you can see 2 IPs appear only one time each, so I would like to count only these IPs. I don't want to count any IP address that appear more than once. How can I achieve that ? I believe the COUNT(DISTINCT ip) will output 4, but I am expecting 2

The following code gives total count of rows in last 30 days,

$result = $dbh->query('SELECT count(*) as total from data_able WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
$total = $result->fetchColumn();

How can I modify it to count unique IPs that appear only 1 time in the last 30 days.

Answer 1

In order to find those ip's occuring only once, you con do this:

SELECT ip FROM data GROUP BY ip HAVING COUNT(ip) = 1;

If you want to count them, you can use the first query.

SELECT COUNT(ip)
FROM (
SELECT ip
FROM data
GROUP BY ip
HAVING COUNT(*) = 1
) AS ip;

db<>fiddle