Get the differrence of a column

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)

Get the differrence of a column

Author

Topic

micheals
Starting Member

1 Post

Posted - 2009-02-19 : 21:46:28

I have 2 tables:

Quiz Table:
QuizID (int)
QuizText (varchar)

User Answer Table
UserAnswerID (int)
QuizID (int)
UserID (int)
Score (int)

Let say I have 10 Quizzes. UserID-50 and UserID-51 have answered all 10 Quizzes. I would like to get the sum of score difference of 2 users based on QuizID
eg User Answer Table:
QuizID = 1
UserID = 50
Score = 4

QuizID = 1
UserID = 51
Score = 1
(Score Diff = 3)

QuizID = 2
UserID = 50
Score = 2

QuizID = 2
UserID = 51
Score = 3
(Score Diff = 1)

QuizID = 3
UserID = 50
Score = 1

QuizID = 3
UserID = 51
Score = 3
(Score Diff = 2)

therefore, Sum of Score Diff = 6

Thanks.
(At one time, I would only like to get score difference of 2 users).

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 22:46:33

[code]Declare @T table
(QuizID int,UserID int,Score int)

Insert @T
Select 1,50,4 union all
Select 1,51,1 union all
Select 2,50,2 union all
Select 2,51,3 union all
Select 3,50,1 union all
Select 3,51,3

Select QuizID,[User50Score],[User51Score],[ScoreDifference],Sum([ScoreDifference]) over() as [SUMOFSCOREDIFF]
from
(Select QuizID,[User50Score],[User51Score],ABS([User50Score]-[User51Score])as [ScoreDifference]
from
(Select QuizID,
Max(Case When UserID = 50 then score Else 0 End)as [User50Score],
Max(Case When UserID = 51 then score Else 0 End)as [User51score]
from @T
Where UserID in (50,51)
Group by QuizID)Z)M[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-20 : 00:00:33

Try this



Declare @Temp table
(QuizID int,UserID int,Score int)

Insert @Temp
Select 1,50,4 union all
Select 1,51,1 union all
Select 2,50,2 union all
Select 2,51,3 union all
Select 3,50,1 union all
Select 3,51,3

select quizid,column1,column2,abs(column1-column2) as diff from (
select quizid,
max(case when userid = 50 then score else 0 end ) as column1,
max(case when userid = 51 then score else 0 end ) as column2
from @Temp group by quizid )t

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 05:46:41

quote:

Originally posted by Nageswar9

Try this



Declare @Temp table
(QuizID int,UserID int,Score int)

Insert @Temp
Select 1,50,4 union all
Select 1,51,1 union all
Select 2,50,2 union all
Select 2,51,3 union all
Select 3,50,1 union all
Select 3,51,3

select quizid,column1,column2,abs(column1-column2) as diff from (
select quizid,
max(case when userid = 50 then score else 0 end ) as column1,
max(case when userid = 51 then score else 0 end ) as column2
from @Temp group by quizid )t

Haven't I shown this already? Please see everything before you post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:47:27

[code]
Select Sum(Case When UserID = 50 then score Else 0 End) AS User50Total,
Sum(Case When UserID = 51 then score Else 0 End) AS User51Total,
Abs(Sum(Case When UserID = 50 then score Else -1*Score End))
as [ScoreDiff]
from @T
Where UserID in (50,51)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources