Using aggregation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using aggregation

Author

Topic

daffyd
Starting Member

3 Posts

Posted - 2005-08-31 : 03:27:16

Hi,

I've been needing to scale a column in a table. Imagine a table (or rather a result of a select) with only positive float numbers in it. I want to scale these numbers so they are all between 0 and 1. So say the select results in:


1.0
2.0
3.0
4.0
5.0

Then I want to convert this to


0.2 
0.4 
0.6 
0.8 
1.0

So I imagine a way to do this is:


SELECT CLS.CL / MAX(CLS.CL) FROM (SELECT ... AS CL ... ) CLS

But this is a invalid use of the aggregate function, which seems logical. But how do I do it without having to evaluate the subquery twice, and without having temporary tables? The reason why I don't want temporary tables is because the whole thing is dynamically generated in ASP.NET and it would be hard to realise.

The real-life query looks more like the following (maybe it helps):


SELECT 
	CL_0, -- <-- This needs normalizing!
	CL_1 
FROM
        (SELECT 
		dbo.__compareFloats(@CL_0,dbo.__convertListToFloat(B.NewsId,16,2),1) AS CL_0, 
		dbo.__nomCompareListToParams(B.NewsId,17,@CL_1) AS CL_1		
	FROM 
		News AS A, 
		News as B 
	WHERE 
		A.TypeId = 48 AND 
		B.TypeId = 2) CLS

Thanks for any help!

Mark

ismail_issac
Starting Member

22 Posts

Posted - 2005-08-31 : 05:57:48

Hi

i find that you are multiplying the numbers with 2

try this out....

select convert(float,(case when left(convert(varchar,ColName*2),1)=1 then
'1.'+ Replace(convert(varchar,ColName*2),'.','') else
'0.' + Replace(convert(varchar,ColName*2),'.','') end)) as Column_Name from TableName

is this what you were looking at????

ismail_issac
Starting Member

22 Posts

Posted - 2005-08-31 : 06:08:29

or something lrke this

select (case when convert(float,left((case when left(convert(varchar,ColName*2),1)=1 then
'1.'+ Replace(convert(varchar,ColName*2),'.','') else
'0.' + Replace(convert(varchar,ColName*2),'.','') end),3)) > 1.0 then

'1.0'
else
left((case when left(convert(varchar,ColName*2),1)=1 then
'1.'+ Replace(convert(varchar,ColName*2),'.','') else
'0.' + Replace(convert(varchar,ColName*2),'.','') end),3)
end) as ColName from TableName

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 06:11:11

Try this

Select CL/(Select max(CL) from yourTable) from yourTable

Madhivanan

Failing to plan is Planning to fail

daffyd
Starting Member

3 Posts

Posted - 2005-08-31 : 07:22:28

quote:
Originally posted by ismail_issac

Hi

i find that you are multiplying the numbers with 2

try this out....

select convert(float,(case when left(convert(varchar,ColName*2),1)=1 then
'1.'+ Replace(convert(varchar,ColName*2),'.','') else
'0.' + Replace(convert(varchar,ColName*2),'.','') end)) as Column_Name from TableName

is this what you were looking at????

I can see your point. The thing is for the same thing the largest value in that column might have been 6 or 78 or 12987. In that case you want to divide by 6, 78 or 12987 not by 5. The reason why the results are * 0.2 is because that is 1/5th, and 5 was the largest number.
You always have to divide by the biggest number to insure all values are between 0 and 1.

Cheers though,

Mark

daffyd
Starting Member

3 Posts

Posted - 2005-08-31 : 07:25:31

quote:
Originally posted by madhivanan

Try this

Select CL/(Select max(CL) from yourTable) from yourTable

Madhivanan

Failing to plan is Planning to fail

Hello,

I agree that this comes to the right result, but it would evaluate yourTable twice. And yourTable is very expensive to evaluate. I know I could put it in a table variable or something but I would prefer it to find a solution which will solve it in one select (without having to evaluate it twice).

Does this make sense?

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 08:45:42

I think you need to use Two Selects

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources