CODEWITHSUNDEEP
mysqlsql
AndF
AndF

Reputation: 33

How to make a query that may combine COUNT(*) from one and some attributes from the other one

I'm starting to learn some SQL and at the moment I'm having some issues making a query. I have two tables:

  1. coursetbl: This table has a courseSectionNum, StudentID, courseID, classroom, meetDays, sitNum, beginTime, endTime. I'm interested in courseSectionNum, classroom, meetDays, and both the times for records that have a courseID of 240.

  2. enrolltbl: This table has courseSectionNum, courseID, StudentID, and grade. I'm only interested in records that have 240 as the courseID.

The query must have the columns of interest in coursetbl and a column of the total amount of students enrolled in each section.

I have separately done each one trying to figure out but I can't. This is what I did for each query:

SELECT courseSectionNum
       , classroom
       , meetDays
       , beginTime
       , endTime 
FROM coursetbl 
WHERE courseID = 240;

This next one is for students in each section.

SELECT courseSectionNum
      , COUNT(*) AS Total 
FROM enrolltbl 
WHERE courseID = 240 
GROUP BY courseSectionNum;

The issue is that I'm not sure how to combine these two tables to make the query I described above.

Any help will be appreciated. Thank you

Upvotes: 1

Views: 321

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

I can imagine an update join possibly being along the lines of what you want here:

SELECT
    c.courseSectionNum,
    c.classroom,
    c.meetDays,
    c.beginTime,
    c.endTime,
    COALESCE(e.Total, 0) AS Total
FROM coursetbl c
LEFT JOIN
(
    SELECT courseSectionNum, COUNT(*) AS Total
    FROM enrolltbl
    WHERE courseID = 240
    GROUP BY courseSectionNum
) e
    ON e.courseSectionNum = c.courseSectionNum
WHERE
    c.courseID = 240;

Upvotes: 1

Related Questions