October 29, 2010 at 3:21 pm
Hello All,
I have a query which returns 1000 rows.
Example:
1111.root.abc---el-paso
124521.root.abc---el-paso
0007.root.abc---el-paso
531.root.el-paso-chase
3587.root.el-paso-chase
3942.root.el-paso-chase
If you look at this query you can tell there are 3 entries for abc---el-paso and 3 entries for el-paso-chase.
How can i group them and show a count.
October 29, 2010 at 3:25 pm
With no test data you'll have to adapt this to your DDL/structure, but this is one approach:
SELECT
REVERSE( LEFT( REVERSE(Col1), CHARINDEX( '.', REVERSE(Col1) ) -1 ) ),
count(*) as NumOccur
FROM
table
GROUP BY
REVERSE( LEFT( REVERSE(Col1), CHARINDEX( '.', REVERSE(Col1) ) -1 ) )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 3:50 pm
Thank You! It works perfect!
October 29, 2010 at 6:21 pm
If you always have 3 parts, the following will do the trick. I've not tested it to see if it's any faster but I do know that 3 REVERSEs are a bit expensive performance wise.
SELECT RightSegment = PARSENAME(Col1,1),
Occurrences = COUNT(*)
FROM dbo.YourTable
GROUP BY PARSENAME(Col1,1)
--Jeff Moden
October 29, 2010 at 9:37 pm
Dang Jeff, much nicer than mine. I'd forgotten that even existed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 30, 2010 at 7:46 am
Nah... I've seen your code on other threads. I just got lucky here. If the data had 5 elements, our code would have been a whole lot more similar. 🙂
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply