Median or Average function for char(10) = '0-5'

Question

Median or Average function for char(10) = '0-5'

mario17

SSCertifiable

Points: 6079
More actions
December 12, 2013 at 9:25 am

#395979

Hi,
I have this setup and need to do calc on this columns, is there any anylitical function that I can apply directly to get int=2.5 fro this
I can play with '-' as delimiter to extract 0,5, but think maybe better solution.
And for @d it actually coming how to convert '.5' into int=.5, direct convert,cast doesn't work.
declare @C varchar(10) = '0-5'
declare @d varchar(10) = '.5'
select avg(@c) to be int=2.5 --??? how to do this
select avg(@d) to be int=.5
Tx
Mario

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Can you be more specific about what you are trying to do? The words you used give me nothing in the way of a clue as to exactly what you are looking to accomplish. Are you needing to do math on a character column that may have other than numeric characters? What's the meaning of a value with '0-5' as a string? I can't determine from your post what your objective is...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

mario17 (12/12/2013)
Hi,
I have this setup and need to do calc on this columns, is there any anylitical function that I can apply directly to get int=2.5 fro this
I can play with '-' as delimiter to extract 0,5, but think maybe better solution.
And for @d it actually coming how to convert '.5' into int=.5, direct convert,cast doesn't work.
declare @C varchar(10) = '0-5'
declare @d varchar(10) = '.5'
select avg(@c) to be int=2.5 --??? how to do this
select avg(@d) to be int=.5
Tx
Mario

You really need to post details so we have an idea of what you are trying to do.

There is absolutely no chance that you will ever get an int = 2.5 😉

I took the liberty of turning this into something readily consumable.

create table #Something

(

SomeKey int identity not null,

SomeValue varchar(10)

)

insert #Something

select '0-5' union all

select '.5';

I have no real idea what you are doing but I think maybe you have delimited values that you want to get the average of. The real issue there is that you are storing delimited values. This is in direct violation of 1NF. To combat this you will have to parse you values first. The best way to do that is to look at the link in my signature about splitting strings.

Assuming you read and understand that article you will find the DelimitedSplit8K function in there. To use this with your data you would do something like this:

select SomeKey, SomeValue, AVG(cast(Item as numeric(9,2))) as AvgValue

from #Something

cross apply dbo.DelimitedSplit8K(SomeValue, '-')

group by SomeKey, SomeValue

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mario17 SSCertifiable Points: 6079 More actions · Answer 3

Thanks, Sean

I just need extract numerical values for those fields, need it for calculations

Tx

Mario

pedro.v.cardoso SSC Enthusiast Points: 158 More actions · Answer 4

declare @C varchar(10) = '0-5'
declare @d varchar(10) = '.5'
select avg(@c) to be int=2.5 --??? how to do this
select avg(@d) to be int=.5

'0-5' I think is 0+1+2+3+4+5 Right ? Average is Iqual to 2.5

Even in dreams Int cannot be 2.5 but simple 2. To get 2.5 you need to use at least decimal(p,s) but maybe you prefer Float

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Despite appearing to get the solution they desired via Sean's post, the OP never got around to actually specifying the exact conditions he was working with, and everything had to be assumed. We really should STOP RESPONDING to those kinds of posts with anything other than a request for more details, so that we can eliminate the need for an assumption. This could as easily have been something else entirely. If we are unwilling to help others learn how to more appropriately communicate, then we should just expect that kind of lack of communication to continue unabated. After all, the entirety of the IT universe MUST rely on accurate communication, and this series of posts is a pretty good example of an OP being either unwilling or unable to do so. Just my two cents, folks...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

You might want to take a look at this article:

Condensing a Delimited List of Integers in SQL Server[/url]

Somewhere buried deep into it (perhaps 2/3s of the way down if I recall) is a way to explode a condensed delimited list of integers like this: 0-5.

You may need to play around with it to make it handle the case when decimals are included in the number, which of course begs the question what if your string looks like this: 0.5-3.5 (what result are you looking for?).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St