CODEWITHSUNDEEP
sqloracle-database
lexytaylormb
lexytaylormb

Reputation: 17

Oracle SQL beginner, I need to join a few columns from 2 tables and use a count for a total result

Oracle SQL: I have data from 2 tables listed here, these are examples as there are many rows for each table. table Instructor:

INSTRUCTOR_ID SALUTATION FIRST_NAME LAST_NAME STREET_ADDRESS ZIP PHONE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE
101 Mr Fernand Hanks 100 East 87th 10015 2125551212 ESILVEST 01/02/1999 ESILVEST 01/02/1999
102 Mr Tom Wojick 518 West 120th 10025 2125551212 ESILVEST 01/02/1999 ESILVEST 01/02/1999
103 Ms Nina Schorin 210 West 101st 10025 2125551212 ESILVEST 01/02/1999 ESILVEST 01/02/1999
104 Mr Gary Pertez 34 Sixth Ave 10035 2125551212 ESILVEST 01/02/1999 ESILVEST 01/02/1999

Also, table for section:

SECTION_ID COURSE_NO SECTION_NO START_DATE_TIME LOCATION INSTRUCTOR_ID CAPACITY CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE
79 350 3 04/14/1999 L509 107 25 CBRENNAN 01/02/1999 CBRENNAN 01/02/1999
80 10 2 04/24/1999 L214 102 15 CBRENNAN 01/02/1999 CBRENNAN 01/02/1999
81 20 2 07/24/1999 L210 103 15 CBRENNAN 01/02/1999 CBRENNAN 01/02/1999
82 20 4 05/03/1999 L214 104 15 CBRENNAN 01/02/1999 CBRENNAN 01/02/1999

I am trying to get results from both tables: first_name, last, name and I need to get the total number of sections in descending order for the total number of sections that each instructor teaches. I am confused. Please be gentle, I am a beginner with SQL!

So far I have tried several instances over the past hour to hour and a half. I am obviously making this harder than it really is. This is the last syntax I tried:

SELECT I.INSTRUCTOR_ID, 
    I.FIRST_NAME,
    I.LAST_NAME
    COUNT(S.SECTION_NO)
FROM INSTRUCTOR I
JOIN SECTION S 
on I.INSTRUCTOR_ID = S.INSTRUCTOR_ID
ORDER BY NUM_SECTIONS;

Upvotes: 1

Views: 37

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Query you posted looks OK, but is missing a few things:

  • a comma (after last_name)
  • alias for the count function (as you're trying to sort data by it)
  • group by clause because all non-aggregated function must be contained in that clause
  • desc (as you said you want to sort result in descending order)

So:

  SELECT i.instructor_id,
         i.first_name,
         i.last_name,
         COUNT (s.section_no) num_sections
    FROM instructor i JOIN section s ON i.instructor_id = s.instructor_id
GROUP BY i.instructor_id, i.first_name, i.last_name
ORDER BY num_sections DESC;

Upvotes: 2

Related Questions