Reputation: 4118
I am trying to get teacher FIRSTNAME
, LASTNAME
and count of courses he got. Oracle database. CLASS
table has columns: CLASSID
, TEACHERID
, CLASSNAME
Current code:
SELECT DISTINCT FIRSTNAME, LASTNAME, COUNT(TEACHERID)
FROM PERSON
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID
INNER JOIN CLASS C ON T.TEACHERID = C.TEACHERID
WHERE T.TEACHERID = C.TEACHERID;
Where is my mistake?
Upvotes: 0
Views: 413
Reputation: 161
May be you can try
SELECT FIRSTNAME, LASTNAME,
(select COUNT(C.TEACHERID) form CLASS C ON T.TEACHERID = C.TEACHERID)
as COURSECOUNT
FROM PERSON
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID
Upvotes: 0
Reputation: 495
you are missing the GROUP BY clause after the WHERE clause
GROUP BY FIRSTNAME, LASTNAME --all the selected columns except those in aggregate functions such as COUNT
Upvotes: 0
Reputation: 1067
Sounds like you need a GroupBy:
SELECT FIRSTNAME, LASTNAME, COUNT(T.TEACHERID)
FROM PERSON
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID
INNER JOIN CLASS C ON T.TEACHERID = C.TEACHERID
GROUP BY LASTNAME,FIRSTNAME
By the way, I initially deleted this answer because it's not a good way to do the grouping. I'd instead prefer grouping by TEACHERID
, then joining back to get the names, rather than grouping by names.
I think this is a better approach:
SELECT FIRSTNAME, LASTNAME, C.NUMCLASSES
FROM PERSON
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID
INNER JOIN (SELECT TEACHERID, COUNT(CLASSID) AS NUMCLASSES FROM CLASS GROUP BY TEACHERID) C
ON C.TEACHERID=T.TEACHERID
Upvotes: 1