mysql how to count class A IPs from a list and return those with a given count

Question

I have a table of class B IPs.
Example table:

The total records for each class A are random, some few, some many.
I need to determine how many class As have 256 entries in the table (meaning the entire class A is contained in the table).
So I need to select each class A in the table like 1.%, 2.%, etc
Then count how many records exist for that particular class A
Then return entries that have a count of 256.
Example output showing Class A 1, 14, and 242 having 256 entries:
1
14
242
Thanks in advance.

Please provide some sample data and expected output – Fathah Rehman P Aug 27 '15 at 17:34 — Fathah Rehman P, Aug 27 '15 at 17:34

Mike Brant · Accepted Answer · 2015-08-31T14:56:56.653

1

This query might not perform well as it would not be able to utilize any existing index, but it should do the trick for you. Here, you are creating a class_a field from your raw ip address field (assumed name here is 'ip'), counting the number of distinct class b values for each class_a field value, and then only returning the cases where the count of class b values = 256.

SELECT
    SUBSTRING_INDEX(ip, '.', 1) AS class_a,
    COUNT(DISTINCT ip) as class_b_count
FROM table
GROUP BY class_a
HAVING class_b_count = 256

This would work even if you do not enforce a unique value on the underlying ip address field. If you already have this constraint, you could drop the DISTINCT keyword within COUNT() function

I would however suggest that you might break apart your ip address into parts if this is a query you are going to be running regularly such that you could leverage indexes.

edited Aug 31 '15 at 14:56

answered Aug 27 '15 at 18:39

Mike Brant

70,514
10
99
103

I won't run this regularly so efficiency isn't a factor. However I can't get it to work. I tried many forms. Adding a missing parenthesis ... swapping group and where ... removing stuff from right to left. If I just run this bit: SELECT SUBSTRING_INDEX(o2ip, '.', 1) AS class_a, COUNT(DISTINCT(SUBSTRING_INDEX(o2ip, '.', -1))) as class_b_count FROM octet2; ... then I get class_a = 1 and class_b_count = 256 ... yet there are only 144 entries for class a = 1 in the table?!? – Xi Vix Aug 27 '15 at 18:50
got it ... SELECT SUBSTRING_INDEX(o2ip, '.', 1) AS class_a, COUNT(DISTINCT(SUBSTRING_INDEX(o2ip, '.', 2))) as class_b_count FROM octet2 group by class_a having class_b_count = 256; – Xi Vix Aug 27 '15 at 18:57
DISTINCT should be keyword like `COUNT(DISTINCT ...)` as shown in my example (I did have errant parenthesis in there when first posting which I corrected. – Mike Brant Aug 27 '15 at 18:58
@XI Vix Ah. so just using the first two segments from left as opposed to only first segment from right did the trick? Weird that it wouldn't have worked the way I showed. I guess perhaps it was taking all distinct values across the table rather than just baed on class-a grouping. I will adjust my answer, which will actually reflect just use of ip field (not need for SUBSTRING_INDEX at this point. – Mike Brant Aug 27 '15 at 18:59
Mike ... there is still a problem with your corrected answer ... when I run this ... SELECT SUBSTRING_INDEX(o2ip, '.', 1) AS class_a, COUNT(DISTINCT o2ip) as class_b_count FROM octet2 GROUP BY class_a WHERE class_b_count = 256; ... I get this error ... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE class_b_count = 256' at line 1 ... but it works when I replace WHERE with HAVING like this ... GROUP BY class_a HAVING class_b_count = 256; – Xi Vix Aug 28 '15 at 21:19
modified working query: SELECT SUBSTRING_INDEX(o2ip, '.', 1) AS class_a, COUNT(DISTINCT o2ip) as class_b_count FROM octet2 GROUP BY class_a HAVING class_b_count = 256; – Xi Vix Aug 28 '15 at 21:25
@XiVix Yep. I actually originally meant to put HAVING there (thus it being after the grouping, because you can't apply WHERE before grouping is applied and get your answer). Don't know why I typed WHERE :P – Mike Brant Aug 31 '15 at 14:56

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

I think you can split the column data with a dot using this issue function:
split-value-from-one-field-to-two

SPLIT_STR(class_a, '.', 1) as class_a

OR save the first param (the A) in a seperated column and use that column in your queries.

Then use a count query grouped by the 'A' class Column like this:

SELECT *, COUNT(A) AS cnt_a FROM class_a 
GROUP BY COUNT(A)
WHERE COUNT(A) = 256

edited May 23 '17 at 12:22

Community

1
1

answered Aug 27 '15 at 18:21

Tariq

2,853
3
22
29

Why use a custom function when existing built-in MySQL string functions will do the trick just fine? – Mike Brant Aug 27 '15 at 18:42
Since when this function exists :0) Depending on that issue, you need to use a user defined function. – Tariq Aug 27 '15 at 18:52
`SUBSTRING_INDEX()` works perfectly for this application. https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index – Mike Brant Aug 27 '15 at 18:54
Thanks for the new info – Tariq Aug 27 '15 at 18:56

mysql how to count class A IPs from a list and return those with a given count

2 Answers2