Count the number of rows with same last part

Question

Post reply

Count the number of rows with same last part

chaudharyabhijit

Ten Centuries

Points: 1314
More actions
October 29, 2010 at 3:21 pm

#143960

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.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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 ) )

- Craig Farrell

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

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 2

chaudharyabhijit

Ten Centuries

Points: 1314

October 29, 2010 at 3:50 pm

#1244460

Thank You! It works perfect!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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

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)
Intro to Tally Tables and Functions

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Dang Jeff, much nicer than mine. I'd forgotten that even existed.