CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
IntoTheDeep
IntoTheDeep

Reputation: 4118

SQL find teacher courses query

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

Answers (3)

grondon
grondon

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

Mentor
Mentor

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

Kevin Fichter
Kevin Fichter

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

Related Questions