• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Getting a breakdown within the same table

I'm having some difficulty trying to get what I think is a simple query to work correctly.
The idea is that I need to get a difference in scoring from other scorers in the same table.
I will need to add a parameter to get the scorer who I want to be the primary scorer etc.
I've attached what I need from what the table looks like.

thanks in advance with your help


Scorer-excel.jpg
score-table.jpg
score-layout.jpg
0
wayneburr
Asked:
wayneburr
  • 2
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT A.*, b.ScorerID, b.Score, SpecificScore-Score as Difference
FROM (
select StudentID,scorerID SpecificScore, Score
FROM Score
WHERE scorerID = @SpecificScorer
)A
INNER JOIn (
select StudentID,scorerID Score
FROM Score
WHERE scorerID = @SecondaryScorerID ) B
ON A.StudentID = B.StudentID
0
 
wayneburrAuthor Commented:
That got me a closer to what I need.  Closer then what I had.
One thing is that I do not and would not know is who the SecondaryScorer is.  I just need to get everybody who scored the same DistrictStudentID as the one who I specify.
So, specificScorer is supplied and I need all other Scorers who scored the same DistrictStudentID and I get the difference score.

thanks.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i think this will do
 

SELECT A.*, b.ScorerID, b.Score, SpecificScore-Score as Difference 
FROM ( 
select StudentID,scorerID SpecificScore, Score 
FROM Score 
WHERE scorerID = @SpecificScorer 
)A 
INNER JOIn ( 
select StudentID,scorerID Score 
FROM Score 
WHERE scorerID <> @SpecificScorer ) B 
ON A.StudentID = B.StudentID

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris LuttrellSenior Database ArchitectCommented:
That is on the right track but I think the query can be more concise without the subselects, like below.
SELECT A.DistrictStudentId, A.ScorerID, A.Score, B.ScorerID, B.Score, B.Score-A.Score as Difference 
FROM Score A 
INNER JOIN Score B ON A.DistrictStudentId= B.DistrictStudentIdAND A.ScorerID <> B.ScorerID
WHERE A.ScorerID= @SpecificScorer 

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
A space got lost in that query and would have caused an error, so I reposted
SELECT A.DistrictStudentId, A.ScorerID, A.Score, B.ScorerID, B.Score, B.Score-A.Score as Difference 
FROM Score A 
INNER JOIN Score B ON A.DistrictStudentId = B.DistrictStudentId AND A.ScorerID <> B.ScorerID
WHERE A.ScorerID = @SpecificScorer 

Open in new window

0
 
wayneburrAuthor Commented:
You guys are good.  Thanks a ton!  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now