SQL how to find the difference of GPA without duplication

Question

I am trying to find the difference of GPA among Semesters.

ID 1 Semester Fall GPA: 3.0
ID 1 Semester Fall GPA: 3.0
ID 1 Semester Spring GPA: 3.5
ID 2 Semester Fall GPA: 3.0
ID 3 Semester Fall GPA: 3.0
ID 3 Semester spring GPA: 3.2

Because ID 2 does not have spring GPA, i would ignore ID2. So, how should i write to find out the difference GPA for ID 1 and ID 3?

ID 1 : .5

ID 3 : .2

I used this code below

select a.id, 
b.gpa - a.gpa as diff
from your_table a
join your_table b on a.id = b.id
where a.semester = 'Fall'
and b.semester = 'Spring';

however, the result shows ...

ID1 : .5
ID1 : .5
ID3 : .2

how can I remove the duplication? to get the resut as...

ID1 : .5 ID3 : .2

I am thinking about 'Distinct'?

Ya . you can use distinct like this query format `select distinct column_name` — Bhanuchander Udhayakumar, Oct 23 '17 at 06:32
Why are there seemingly duplicate rows in the first place? Is there some additional info missing or does the table itself contain duplicates? — QHarr, Oct 23 '17 at 06:36
Question is duplicated, please refer to https://stackoverflow.com/questions/1641718/how-to-select-unique-records-by-sql — Louisth, Oct 23 '17 at 07:53

score 0 · Answer 1 · answered Oct 23 '17 at 06:41

0

If you want to remove duplicate, use distinct

SELECT DISTINCT a.id
    ,b.gpa - a.gpa AS diff
FROM table1 a
INNER JOIN table1 b ON a.id = b.id
WHERE a.semester = 'Fall'
    AND b.semester = 'Spring';

DEMO

answered Oct 23 '17 at 06:41

zarruq

2,445
2
10
19

SQL how to find the difference of GPA without duplication

1 Answers1