-1

meetings which looks like this:

meeting_ID place
1 A
2 B
3 C

study_groups which looks like this:

study_group_id meeting_id (fk) topic
1 1 Basics
2 2 Photography
3 1 Film

group_members which looks like this:

study_group_id (fk) student_id (fk)
2 10
1 1
2 5
3 15
1 9
3 2
3 11

A meeting has many study_groups and each study_groups has many students.

I want to find number of student in each group individually for meeting_id = 1.

This is my SQL query:

SELECT
    study_groups.study_group_id,
    study_groups.topic,
    study_groups.description,
    study_groups.group_member_limit,
    (SELECT COUNT(group_members.student_id)
     FROM group_members, study_groups
     WHERE study_groups.study_group_id = group_members.study_group_id
       AND study_groups.meeting_id = 1) AS no_of_students
FROM 
    study_groups
WHERE 
    study_groups.meeting_id = 1;

This is returning:

study_group_id no_of_student topic
1 5 but want 2 Basics
2 5 but want 3 Photography

Please correct my SQL kindly for the expected result.

Additional

Please also suggest me what should I should do if I want to see the student details(eg:name, email) in the same query, is it possible?

Where student table is

student_id name email
1 xyz xyz@exmpl.com
2 abc abc@exmpl.com

I want the result as:

study_group_id no_of_student topic name
1 5 but want 2 Basics xyz, abc (demo)
2 5 but want 3 Photography abc, xyz (demo)
Sumon SUtrodhar
  • 3
  • 2

2 Answers2

0

You don't need the subquery. Just use a count distinct on group_members.student_id and add a group by statement that includes all the other columns in the main select query

NickW
  • 8,430
  • 2
  • 6
  • 19
0

You are on the right back with a subquery, but you want a correlated subquery. This looks like:

SELECT sg.study_group_id, sg.topic, sg.description, sg.group_member_limit,
       (SELECT COUNT(*)
        FROM group_members gm
        WHERE sg.study_group_id = gm.study_group_id 
       ) AS num_students
FROM study_groups sg
WHERE sg.meeting_id = 1;

The important part of the query is that study_groups is removed from the subquery so the subquery refers to the outer query.

I also introduced column aliases so the query is easier to write and to read.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786