List:General Discussion« Previous MessageNext Message »
From:Mikael Fridh Date:December 8 2003 2:58pm
Subject:Re: manipulating count group by statement
View as plain text  
Hi, I tried this and it seems to work:
SELECT SUBSTRING_INDEX(INET_NTOA(ip), '.',3) AS addr, COUNT(*) AS addrsum FROM 
ips GROUP BY addr;

Perhaps you don't have your IP adresses stored as integers but instead as 
text. Then the following query is for you!

Your query:
SELECT ip, COUNT(*) from tbl_MasterIp group by ip;

Would then be something like this:
SELECT SUBSTRING_INDEX(ip, '.', 3) AS ip, COUNT(*) AS addrsum FROM 
tbl_MasterIp Group By ip;

Good luck,
Mike

On Monday 08 December 2003 15.23, chad kellerman wrote:
> Hello everyone,
>
>    I have a mysql table tha holds a variety of ip address.  I am trying
> to work out a select statement that will give me a count(*) grouped by
> the first 2 octets of the ip.
>
> if the ip table contains:
>
> 10.102.1.1
> 192.168.123.34
> 192.168.123.4
> 192.168.123.43
> 192.168.123.3
> 192.168.0.27
> 192.168.0.212
> 10.102.1.54
> 10.102.1.75
> 10.102.1.62
> 10.102.1.12
> 10.102.1.10
> 10.102.1.111
>
> how can my:
> SELECT ip, COUNT(*) from tbl_MasterIp group by ip;
>
> be changed so that I get back
>
>    ip        COUNT(*)
> 10.102.1       20
> 192.168.123    30
> 192.168.0      10
>
> or whatever the ip counts  should be.  I have about 5 different octets
> within this table.
>
> I am using mysql 3.*
>
> Thanks for any help...
>
>
>
> Sincerely,
> Chad

Thread
manipulating count group by statementchad kellerman8 Dec
  • Re: manipulating count group by statementMikael Fridh8 Dec