SQL Query 2 tables

Question

I have 2 tables: teacher, & class. here's the descriptions:

TEACHER
 ----------------
 TEACHER_ID
 FIRST_NAME
 LAST_NAME
 TYPE_CODE
 HOME_ROOM_NUM
 PHONE_NUM
 START_DATE
 HOME_STATE
 SCHOOL_ID

 Class
 --------------------
 CLASS_ID
 CLASS_NAME
 TEACHER_ID
 MAX_SEATS_AVAILABLE

Im trying to figure out how to count the number of classes a given teacher teaches. here's what I have tried: 1. How many classes does Lisa Jones teach, if any?

SQL> select teacher.last_name, teacher.first_name, class.class_name as 
  2  from teacher, class
  3  where teacher.teacher_id = '2'
  4  AND class.teacher_id = '2';

here's the result I get:

LAST_NAME         FIRST_NAME        CLASS_ID CLASS_NAME           TEACHER_ID  MAX_SEATS_AVAILABLE

----------------- ----------------- -------- -------------------- ----------- --------------------
JONES             LISA               2       Basic CALCULUS       2           10
JONES             LISA               9       Physics 230          2           20

I just need teacher name, Id, and # of classes, not having any luck w/ the COUNT function.

score 4 · Answer 1 · answered Sep 23 '13 at 02:58

I suggest eliminating the deprecated implicit joins and replacing with standard explicit joins: Just need a GROUP BY to get the COUNT() to work properly.

SELECT t.last_name, t.first_name, t.Teacher_ID, COUNT(DISTINCT CLASS_ID) as Classes_Count 
FROM teacher t
JOIN class c
  ON t.Teacher_ID = c.Teacher_ID
WHERE t.Teacher_ID = '2'
GROUP BY t.last_name, t.first_name, t.Teacher_ID

You may or may not want DISTINCT in the count, depending on the nature of your data.

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

I think the @GOAT CO solution is correct. Thi squery is working without JOIN statement:

select t.last_name, t.first_name, c.class_name, COUNT(DISTINCT c.CLASS_ID) as Classes_Count from teacher t, class c where t.teacher_id = c.teacher_id and t.teacher_id = 1 GROUP BY t.LAST_NAME, t.FIRST_NAME, t.TEACHER_ID;

EDIT: Now I add alias for Teacher and Class table and add group statement to script. Can you try it? I tested only on Fiddle.

Here is SQL FIDDLE link.

EDIT2: Meanwhile I check ORA-00937: not a single-group group function and found this and this topic. But this script don't get this exception. If you modified statement please share it!

edited May 23 '17 at 12:20

Community

1
1

answered Sep 23 '13 at 08:00

herry

1,708
3
17
30

I keep getting an error: select teacher.last_name, teacher.first_name, class.class_name, COUNT(DISTINCT CLASS_ID) as Classes_Count * ERROR at line 1: ORA-00937: not a single-group group function – Brian Wilson Sep 24 '13 at 17:58
Your query still has a join in it - but it's an outdated, old-style implicit join in the where clause. – Sep 25 '13 at 07:55

SQL Query 2 tables

2 Answers2