ip_available Table data is:
| ip_address | parent |
|---|---|
| 10010010024 | 1987 |
| 10010010026 | 1987 |
| 10010010027 | 1987 |
| 10010010033 | 1987 |
| 10010010034 | 1987 |
| 10010010035 | 1987 |
| 10010010036 | 1987 |
| 10010010037 | 1987 |
| 10010010090 | 2010 |
Similarly there are more ip_addresses with other parent values
I want result set like
| ip_address_min | parent | count |
|---|---|---|
| 10010010024 | 1987 | 3 |
| 10010010033 | 1987 | 5 |
| 10010010090 | 2010 | 1 |
DB is MySQL Count should come for each regular sequence, No matter if parent value is repeated.
CodePudding user response：
A little more details would help out next time, like including your table structure. But I believe this will help you out.
SELECT MIN(ip_address) as ip_address_min, MIN(parent) as parent, COUNT(ip_address) as count
FROM ip_available
GROUP BY LEFT(ip_address,length(ip_address)-1);