CODEWITHSUNDEEP
sqlsql-server
L.G
L.G

Reputation: 29

Extracting Data from two tables in SQL

I have two tables

Table 1.

Name         Grade   Math  
---------------------------    
Chris          A      30
Julie          AA     35
Ross           AA     32

Table 2

Name       English
-------------------------    
Julie      29
Chris      22
Ross       20

I want to get the sum of the scores for English of students with AA grade for their math. How can I do that? Please help.

EDIT: I want to get the sum of English Scores of students with AA grades for Math, i.e for Julie and Ross: sum of English scores would be 29+20.

Upvotes: 0

Views: 241

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

The straigh-forward way to get data from one table where something is given in another table is to use EXISTS or IN.

select sum(english)
from table2 t2
where exists
(
  select *
  from table1 t1
  where t1.name = t2.name
  and t1.grade = 'AA'
);

With IN:

select sum(english)
from table2
where name in
(
  select name
  from table1
  where grade = 'AA'
);

I like IN much better here, but that's a matter of taste.

Both queries assume that both tables' primary key is name. I asked you to tell us, but you didn't. So again: With another primary key, you might need other queries.

Upvotes: 0

PP006
PP006

Reputation: 709

Try this,

SELECT Sum(B.English) Total
FROM   #Table_1 A
JOIN   #Table_2 B ON A.Name = B.Name
WHERE  Grade = 'AA'

If you want the marks separately use this

SELECT A.Name,
       Sum(B.English) Total
FROM   #Table_1 A
JOIN   #Table_2 B ON A.Name = B.Name
WHERE  Grade = 'AA'
GROUP  BY A.Name

Upvotes: 1

artm
artm

Reputation: 8584

Try

SELECT SUM(t2.English)
FROM Table1 t1
JOIN Table2 t2
ON t1.Name = t2.Name
WHERE t1.Grade = 'AA'

Upvotes: 1

Related Questions