help with sql syntax code from VB

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 2005 Forums

Transact-SQL (2005)

help with sql syntax code from VB

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-15 : 03:12:42

Hi,

I have the following query, in which I want to add one more column.
The column I want to add is computed from the columns brought back below


SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20', 

COUNT(*) AS TotalVotes FROM tblRandomVote

I want to incorporate the following VB function into this query(convert it to TSQL) and bring the result back as an extra column.

I'm not very good with tsql and not sure the best way to do this. Hopefully that makes sense, if theres any questions please let me know!

Any help is very much appreciated !

thanks again,
mike123



Public Function showRootMeanSquareAverage2(ByVal votes_10 As Integer, ByVal votes_15 As Integer, ByVal votes_20 As Integer, ByVal totalVotes As Integer) As String

            votes_10 = votes_10 * (10 * 10)
            votes_15 = votes_15 * (15 * 15)
            votes_20 = votes_20 * (20 * 20)

            Dim result As Double = Sqrt(CDbl((votes_10 + votes_15 + votes_20)) / CDbl(totalVotes))

            result = result / 10

            Dim finalResult As String = CStr(Left(result, 5))

            Return finalResult

        End Function

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-15 : 03:36:19

Are there multiple places where this calculation needs to be done? If not, then you can just place the calculation code inline in the query.

Select
	(SQRT((votes_10 * 100.00) + (votes_15 * 225.00) + (votes_20 * 400.00)) / TotalVotes) / 10
From
(
SELECT
	ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
	ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
	ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20', 
	COUNT(*) AS TotalVotes 
FROM tblRandomVote  
) t

I didn't quite understand why you are doing LEFT(result, 5)?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 04:21:03

<<
I didn't quite understand why you are doing LEFT(result, 5)?
>>

It seems he/she wants 'votes' only

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-15 : 04:40:13

hey thanks very much for the help, I'm working it into my code now..

I'm having some different results brought back than expected but your codelooks right and I think its something I'm doing wrong.

BTW the vb.net code "LEFT(result, 5)" is just to present a more readable number to 4 decimal places.

Thanks very much!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-15 : 04:58:35

Can anyone see where I am going wrong here? The 'average' result is not coming back with the desired result, somehow I am messing up the calculation or hte selection. It's working fine in my VB, which I have much more experience and control over :)

thanks again!!
mike123




alter PROCEDURE [dbo].[select_VoteHistory]  
	 AS SET NOCOUNT ON 

 
Select

t.votes_10,
t.votes_15,
t.votes_20,
t.votes_25,
t.votes_30,
t.votes_35,
t.votes_40,
t.votes_45,
t.votes_50,
t.votes_55,
t.votes_60,
t.votes_65,
t.votes_70,
t.votes_75,
t.votes_80,
t.votes_85,
t.votes_90,
t.votes_95,
t.votes_100,
t.totalVotes, 

	
(SQRT( 
	(t.votes_10 * 10 * 10) + 
	(t.votes_15 * 15 * 15) + 
	(t.votes_20 * 20 * 20) +
	(t.votes_25 * 25 * 25) +
	(t.votes_30 * 30 * 30) +
	(t.votes_35 * 35 * 35) +
	(t.votes_40 * 40 * 40) +
	(t.votes_45 * 45 * 45) +
	(t.votes_50 * 50 * 50) +
	(t.votes_55 * 55 * 55) +
	(t.votes_60 * 60 * 60) +
	(t.votes_65 * 65 * 65) +
	(t.votes_70 * 70 * 70) +
	(t.votes_75 * 75 * 75) +
	(t.votes_80 * 80 * 80) +
	(t.votes_85 * 85 * 85) +
	(t.votes_90 * 90 * 90) +
	(t.votes_95 * 95 * 95) +
	(t.votes_100 * 100 * 100) 
) 

/ TotalVotes) / 10 as 'average' 

FROM 
   
(
SELECT SUM(votes_10) as votes_10, SUM(votes_15) as votes_15, SUM(votes_20) as votes_20, SUM(votes_25) as votes_25, SUM(votes_30) as votes_30, 
SUM(votes_35) as votes_35, SUM(votes_40) as votes_40, SUM(votes_45) as  votes_45, SUM(votes_50) as votes_50, SUM(votes_55) as votes_55,
SUM(votes_60) as votes_60, SUM(votes_65) as votes_65, SUM(votes_70) as votes_70, SUM(votes_75) as votes_75, SUM(votes_80) as votes_80,
SUM(votes_85) as votes_85, SUM(votes_90) as votes_90, SUM(votes_95) as votes_95, SUM(votes_100) as votes_100, SUM(TotalVotes) as totalVotes 

FROM

(

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',
ISNULL(SUM ( CASE WHEN points = 25 THEN 1 ELSE 0 END ),0) as 'votes_25',
ISNULL(SUM ( CASE WHEN points = 30 THEN 1 ELSE 0 END ),0) as 'votes_30',
ISNULL(SUM ( CASE WHEN points = 35 THEN 1 ELSE 0 END ),0) as 'votes_35',
ISNULL(SUM ( CASE WHEN points = 40 THEN 1 ELSE 0 END ),0) as 'votes_40',
ISNULL(SUM ( CASE WHEN points = 45 THEN 1 ELSE 0 END ),0) as 'votes_45',
ISNULL(SUM ( CASE WHEN points = 50 THEN 1 ELSE 0 END ),0) as 'votes_50',
ISNULL(SUM ( CASE WHEN points = 55 THEN 1 ELSE 0 END ),0) as 'votes_55',
ISNULL(SUM ( CASE WHEN points = 60 THEN 1 ELSE 0 END ),0) as 'votes_60',
ISNULL(SUM ( CASE WHEN points = 65 THEN 1 ELSE 0 END ),0) as 'votes_65',
ISNULL(SUM ( CASE WHEN points = 70 THEN 1 ELSE 0 END ),0) as 'votes_70',
ISNULL(SUM ( CASE WHEN points = 75 THEN 1 ELSE 0 END ),0) as 'votes_75',
ISNULL(SUM ( CASE WHEN points = 80 THEN 1 ELSE 0 END ),0) as 'votes_80',
ISNULL(SUM ( CASE WHEN points = 85 THEN 1 ELSE 0 END ),0) as 'votes_85',
ISNULL(SUM ( CASE WHEN points = 90 THEN 1 ELSE 0 END ),0) as 'votes_90',
ISNULL(SUM ( CASE WHEN points = 95 THEN 1 ELSE 0 END ),0) as 'votes_95',
ISNULL(SUM ( CASE WHEN points = 100 THEN 1 ELSE 0 END ),0) as 'votes_100',

COUNT(*) AS TotalVotes FROM tblRandomVote  
 
 

UNION 

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 15 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',
ISNULL(SUM ( CASE WHEN points = 25 THEN 1 ELSE 0 END ),0) as 'votes_25',
ISNULL(SUM ( CASE WHEN points = 30 THEN 1 ELSE 0 END ),0) as 'votes_30',
ISNULL(SUM ( CASE WHEN points = 35 THEN 1 ELSE 0 END ),0) as 'votes_35',
ISNULL(SUM ( CASE WHEN points = 40 THEN 1 ELSE 0 END ),0) as 'votes_40',
ISNULL(SUM ( CASE WHEN points = 45 THEN 1 ELSE 0 END ),0) as 'votes_45',
ISNULL(SUM ( CASE WHEN points = 50 THEN 1 ELSE 0 END ),0) as 'votes_50',
ISNULL(SUM ( CASE WHEN points = 55 THEN 1 ELSE 0 END ),0) as 'votes_55',
ISNULL(SUM ( CASE WHEN points = 60 THEN 1 ELSE 0 END ),0) as 'votes_60',
ISNULL(SUM ( CASE WHEN points = 65 THEN 1 ELSE 0 END ),0) as 'votes_65',
ISNULL(SUM ( CASE WHEN points = 70 THEN 1 ELSE 0 END ),0) as 'votes_70',
ISNULL(SUM ( CASE WHEN points = 75 THEN 1 ELSE 0 END ),0) as 'votes_75',
ISNULL(SUM ( CASE WHEN points = 80 THEN 1 ELSE 0 END ),0) as 'votes_80',
ISNULL(SUM ( CASE WHEN points = 85 THEN 1 ELSE 0 END ),0) as 'votes_85',
ISNULL(SUM ( CASE WHEN points = 90 THEN 1 ELSE 0 END ),0) as 'votes_90',
ISNULL(SUM ( CASE WHEN points = 95 THEN 1 ELSE 0 END ),0) as 'votes_95',
ISNULL(SUM ( CASE WHEN points = 100 THEN 1 ELSE 0 END ),0) as 'votes_100',

COUNT(*) AS TotalVotes FROM tblUserVote  
 
 
  
) a 

 ) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 09:00:08

Try using


(SQRT( 
	(t.votes_10 * 10 * 10) + 
	(t.votes_15 * 15 * 15) + 
	(t.votes_20 * 20 * 20) +
	(t.votes_25 * 25 * 25) +
	(t.votes_30 * 30 * 30) +
	(t.votes_35 * 35 * 35) +
	(t.votes_40 * 40 * 40) +
	(t.votes_45 * 45 * 45) +
	(t.votes_50 * 50 * 50) +
	(t.votes_55 * 55 * 55) +
	(t.votes_60 * 60 * 60) +
	(t.votes_65 * 65 * 65) +
	(t.votes_70 * 70 * 70) +
	(t.votes_75 * 75 * 75) +
	(t.votes_80 * 80 * 80) +
	(t.votes_85 * 85 * 85) +
	(t.votes_90 * 90 * 90) +
	(t.votes_95 * 95 * 95) +
	(t.votes_100 * 100 * 100) 
) 

/ TotalVotes) / 10.0 as 'average'

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-15 : 19:51:54

Hi Madhivanan,

Still no luck. I'm wondering if the sql statement is calculating the 'average' result in my desired order? What I am trying to do in order is :

1.) SQUARE all the values
2.) Take the average of the squares
3.) Take the square root of the average

The number is more off than decimal places, its just completely off I can't simulate how its gotten there yet.

Thanks again! very much appreciated !

Mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources