Using COUNT (DISTINCT..) when also using INNER JOIN to join 3 tables but Postgres keeps erroring

Question

I need to use INNER JOINs to get a series of information and then I need to COUNT this info. I need to be able to "View all courses and the instructor taking them, the capacity of the course, and the number of members currently booked on the course."

To get all the info I have done the following query:

SELECT
    C.coursename, Instructors.fname, Instructors.lname,C.maxNo, membercourse.memno
FROM Courses AS C
INNER JOIN Instructors ON C.instructorNo = Instructors.instructorNo
INNER JOIN Membercourse ON C.courseID = Membercourse.courseID;

but no matter where I put the COUNT it always tells me that whatever is outside the COUNT should be in the GROUP BY

I have worked out how to COUNT/GROUP BY the necessary info e.g.:

SELECT courseID, COUNT (DISTINCT MC.memno)
FROM Membercourse AS MC
GROUP BY MC.courseID;

but I don't know how to combine the two!

Answer 1

There are many ways to solve this, such using Window Functions and so on. But you can also achieve it using a simple subquery:

SELECT
    C.coursename, 
    Instructors.fname, 
    Instructors.lname, 
    C.maxNo, 
    (SELECT 
        COUNT(*) 
    FROM 
        membercourse 
    WHERE 
        C.courseID = Membercourse.courseID) AS members
FROM 
    Courses AS C
    INNER JOIN Instructors ON C.instructorNo = Instructors.instructorNo;