CODEWITHSUNDEEP
sqlcountmultiple-tables
DJohnson1990
DJohnson1990

Reputation: 57

Counting - based on output from two tables

I have two tables: master and class. Master is a master list of all students that have ever attended the school with studentID and Social Security Number (SSN). I'm worried that honors students have been assigned multiple student ID's. I would like to count studentID's for each SSN.

select master.studentID, master.SSN, class.studentID, class.type
from master, class
where master.studentID = class.student.ID and class.type = 'Honors" and count(master.StudentID) > 1

I get the following error message: "Use of function COUNT not valid"

Upvotes: 1

Views: 33

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146603

Just turn your where clause predicate into a subquery

select m.studentID, m.SSN, c.studentID, c.type,
    (Select count(*) from master
     Where SSN = m.SSN) ssnCount
from master m join class c on c.studentID = m.studentID 
where c.type = 'Honors"
    and (Select count(*) from master
         where SSN = m.SSN) > 1

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select master.SSN
from master
join class on master.studentID = class.student.ID
where class.type = 'Honors' 
group by master.SSN
having count(distinct master.studentID) > 1

Upvotes: 1

Related Questions