Group by

Question

Post reply

Group by

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
March 11, 2011 at 11:02 am

#235148

Hi All,
Let us say I have a table with a column Varchar(20) that stores IP address. I want to group by the first two numbers in the IP address to get a count. How can I do that.
Script to create, Populate and what the output should be is as below
Create table #tempIPTable
(IPAddress varchar(50));
Insert into #tempIPTable
Select '172.24.28.1'
UNION ALL
Select '223.25.26.1'
UNION ALL
Select '223.25.26.5'
UNION ALL
Select '22.25.26.1'
UNION ALL
Select '22.25.26.4'
UNION ALL
Select '223.25.20.1'
UNION ALL
Select '22.25.23.1'
The output I am looking for is
IPAddress Cnt
172.24 1
223.25 3
22.25 3
Thanks
-Roy

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

SELECT LEFT(ipaddress, CHARINDEX('.', ipaddress, CHARINDEX('.', ipaddress)+1)-1), COUNT(*)

FROM #tempIPTable

GROUP BY LEFT(ipaddress, CHARINDEX('.', ipaddress, CHARINDEX('.', ipaddress)+1)-1);

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

Here's a brute force solution:

SELECT CASE WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,6,1) = '.' THEN Substring(IPAddress,1,5)

WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,8,1) = '.' THEN Substring(IPAddress,1,7)

END AS IPAddress, Count(CASE WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,6,1) = '.' THEN Substring(IPAddress,1,5)

WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,8,1) = '.' THEN Substring(IPAddress,1,7)

END) AS DomainCnt

FROM #tempIPTable

Group By CASE WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,6,1) = '.' THEN Substring(IPAddress,1,5)

WHEN Substring(IPAddress,3,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,7,1) = '.' THEN Substring(IPAddress,1,6)

WHEN Substring(IPAddress,4,1) = '.' AND Substring(IPAddress,8,1) = '.' THEN Substring(IPAddress,1,7)

END

The reason my CASE is so complicated is that you forgot to account for data where you have IPs like 172.126. or IPs like 22.126.

EDIT: I was trying to figure out the CharIndex and PatIndex, but couldn't figure it out fast enough. That's the better solution. Less wonky code.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

Create table #tempIPTable

(IPAddress varchar(50));

Insert into #tempIPTable

Select '172.24.28.1'

UNION ALL

Select '223.25.26.1'

UNION ALL

Select '223.25.26.5'

UNION ALL

Select '22.25.26.1'

UNION ALL

Select '22.25.26.4'

UNION ALL

Select '223.25.20.1'

UNION ALL

Select '22.25.23.1'

SELECT FirstTwo, Count(IPAddress)

FROM

(SELECT IPAddress , SUBSTRING(IPAddress , 1, CHARINDEX('.', IPAddress , CHARINDEX('.', IPAddress ) + 1) - 1) as FirstTwo

FROM #tempIPTable) SubNet

Group By FirstTwo

DROP TABLE #tempIPTable

I used a subquery to prevent having a function in a Group By clause

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Thanks Gus, Brandie, Stefan. I knew that some one of you would come with a solution that was tough for me to figure out. Now you know how bad I am in T-SQL 🙂

EDIT was to add Stefan too to the list of people to Thank. 🙂

-Roy

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Ah. Stefan found the Substring version I was trying to do. I forgot about starting location in CharIndex(). No wonder I couldn't get it to work.

You're welcome, Roy. Glad we could help.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 6

Brandie Tarvin (3/11/2011)
Ah. Stefan found the Substring version I was trying to do. I forgot about starting location in CharIndex(). No wonder I couldn't get it to work.
You're welcome, Roy. Glad we could help.

I could have sworn there's a function that finds the nth occurance of a character, but I couldn't find it. Maybe that's in SSIS or VB.Net or C#? I get confused sometimes.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Lowell SSC Guru Points: 323518 More actions · Answer 7

how about using the PARSENAME function to chop up the IP address?

--using Stefan's setup:

/*--Results

(No column name)(No column name)

1172.24

322.25

3223.25

*/

SELECT Count(IPAddress),parsename(IPAddress,4) + '.' + parsename(IPAddress,3)

FROM #tempIPTable

Group By parsename(IPAddress,4) + '.' + parsename(IPAddress,3)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

You all are awesome. I ask for one solution and I get different solutions... 🙂

-Roy

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 9

Now that's an interesting solution I never would have thought of.

But Parsename() only works because it's treating the IPAddress as a Server.Database.Schema.Object item (because of the periods). It wouldn't work if Roy was using some other data or delimiter. And it "fails" if I stick in data that contains more than 4 periods. It returns a NULL for the value '22.28.23.1.5'.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Lowell (3/11/2011)
how about using the PARSENAME function to chop up the IP address?
--using Stefan's setup:
/*--Results
(No column name)(No column name)
1172.24
322.25
3223.25
*/
SELECT Count(IPAddress),parsename(IPAddress,4) + '.' + parsename(IPAddress,3)
FROM #tempIPTable
Group By parsename(IPAddress,4) + '.' + parsename(IPAddress,3)

Heh... dang it. Ya beat me to it, Lowell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Brandie Tarvin (3/11/2011)
It wouldn't work if Roy was using some other data or delimiter

It only takes one well place REPLACE to fix that little nuance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 12

Jeff Moden (3/11/2011)
Lowell (3/11/2011)
how about using the PARSENAME function to chop up the IP address?
--using Stefan's setup:
/*--Results
(No column name)(No column name)
1172.24
322.25
3223.25
*/
SELECT Count(IPAddress),parsename(IPAddress,4) + '.' + parsename(IPAddress,3)
FROM #tempIPTable
Group By parsename(IPAddress,4) + '.' + parsename(IPAddress,3)
Heh... dang it. Ya beat me to it, Lowell.

Hehe, my sentiments exactly. I was reading through going, "Oh cool, I'll add a solution with parse.... doh!"

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Hey! Looki there! Haven't seen you in a while. How ya doin', Seth?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

Thank You everyone for the different solutions you provided me. I appreciate it very much.

-Roy