Grouping Query
I have a csv file which is exported from another system each day. The file contains various IP addresses with associated usage.
ex
Address Usage
10.15.22.198 1,200,000
10.15.22.198 1,100,000
10.22.15.194 950,000
10.52.18.187 911,000
10.26.12.194 901,000
10.57.83.187 850,000
I need to write a query to pull this data and put into usage bands and count how many are there.
ex.
Between 800,000 and 900,000 1
Between 900,000 and 1,000,000 3
>1,000,000 2
Software/Hardware used:
Software/Hardware used:
ASKED:
June 2, 2009 6:18 PM
UPDATED:
June 25, 2009 4:23 PM
Answer Wiki:
In Excel, the COUNTIF function will give you the answer.
Assume your data above is in a block of cells from A5 to B102.
Create a block of "criteria" cells, say in H6 ... H10 as follows:
H6 800000
H7 900000
H8 1000000
H9 1500000
H10 2000000
(extend as needed)
In the next column (I6 ... I10), enter the formula:
=COUNTIF(A$5:B$102,">="&H6)-COUNTIF(A$5:B$102,">="&H7)
Enter that in I6, then copy and paste down to I10, which will change the references H6 and H7 to the appropriate cells.
Voila - the table in H6:I10 now contains your results with counts.
I am a little puzzled by your source data, which shows the same IP addresses with different counts. If you need to sum the counts for a given IP address first, that is a different question.
To see all answers submitted to the Answer Wiki: View Answer History.
The only reason for this type of data manipulation I can think of is to monitor bandwidth, of traffic recieved by an ip. (We have done this to load balance pc’s on subnets. Too many with a high usage rate in a subnet slows down the others.)
If I’m right then you are trying to rate the traffic to an ip. So each ip’s usage is evaluated and put into a usage class. Try something like this
table cell 1 = usage figure
table cell 2 = IP address
sort highest to lowest on column 1
Then it is just a case of checking if the usage value falls into a defined group, and copying the IP address into that group. You could copy the usage figure as well, just for continuity.
Hope this helps.
Dave
SELECT IIf([usage]>=850000 And [usage]<900000,’Between 850,000 and 900,000′,IIf([usage]>=900000 And [usage]<1000000,’Between 900,000 and 1,000,000′,’>1,000,000′)) AS txt, count(*) AS Qty
FROM YourTable
GROUP BY IIf([usage]>=850000 And [usage]<900000,’Between 850,000 and 900,000′,IIf([usage]>=900000 And [usage]<1000000,’Between 900,000 and 1,000,000′,’>1,000,000′)) ;