Record count 2

[jmandiaz]

Hi All,
I need some help here. I have one table with about 20000 records. The table contains source ip address and destination ip address along with source port and destination port. I want to be able to enter an ip address have the query return how many times the IP address that i entered is on that table. Any suggestion would be greatly appreciated.

-Jaime

 

[PHV]

In the SQL pane of the Query window, you try something like this ?
PARAMETERS [Enter IP address] Text (16);
SELECT Sum(S.CountOfIP) As TotalIP, S.IPaddress FROM
(SELECT Count(*) As CountOfIP, A.srcIP As IPaddress
FROM theTable A
WHERE A.srcIP=[Enter IP address]
GROUP BY A.srcIP
UNION
SELECT Count(*), B.destIP
FROM theTable B
WHERE B.destIP=[Enter IP address]
GROUP BY B.destIP
) As S;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jmandiaz]

Thanks for your PVH
I will try your query and let you know the outcome thanks again

-Jaime

 

[jmandiaz]

PHV,
I was able to get your query to run successfull but i'm not getting the result i'm looking for. Let say for example
in my ip table i have 10.1.1.0 showing 30 times. I want to the query to prompt me the enter the IP address once i enter the ip address i want the result to diplay one record telling me that 10.1.1.0 is on the table 30 times instead of diplaying 10.1.1.0 30 times

 

[lespaul]

Are you sure you entered correctly? PHV's query should do exactly what you asked for. Here's a breakdown of the query:

PARAMETERS [Enter IP address] Text (16);
[COLOR=blue]SELECT Sum(S.CountOfIP) As TotalIP, S.IPaddress FROM[/color] 
[COLOR=red](SELECT Count(*) As CountOfIP, A.srcIP As IPaddress
   FROM theTable A 
  WHERE A.srcIP=[Enter IP address]
 GROUP BY A.srcIP [/color]
UNION
 [COLOR=green]SELECT Count(*), B.destIP
   FROM theTable B
  WHERE B.destIP=[Enter IP address]
 GROUP BY B.destIP [/color]
)[COLOR=blue] As S[/color];

the red section counts all the records where the IP address matches srcIP

the green section counts all the records where the IP address matches destIP

the blue section sums the counts from the red and green queries.

If you run the red and green queries alone do they seem to work correctly?

Leslie

Leslie

 

[jmandiaz]

Leslie,
Thanks for your help again! I guess i fat fingered it the first time.

Regards

-Jaime