SQL Query Count

1

I’m confused about how to write SQL to display industry counts by each major name in a given schedule by id
this is my table relation

image
image871×503 66.2 KB

I try to use this query but did not grouping by major name

SELECT majors.name, count(students_industries.industry_id) as industri FROM majors 
INNER JOIN groups ON majors.id=groups.major_id
INNER JOIN students ON groups.id=students.group_id
INNER JOIN students_industries ON students_industries.student_id= students.id WHERE students_industries.schedule_id = 2 GROUP by majors.name ,students_industries.industry_id;
2

Just looking at your query, the group by is incorrect. Remove students_industries.industry_id; from the group by

3

it will show as many industries as it relates to the student's table

as an example
majors table
id | name
1 | Informatics
2 | Nursing

table of students
id | name
1 | Robert
2 | Danny
3 | Ratna

industries table
id | name
1 | Unilever
2 | Amazons
3 | Google

students_industries table
id | major_id | student_id | industry_id
1 | 1 | 1 | 1
2 | 1 | 2 | 1
3 | 1 | 3 | 1

if I use that query it will display 3 industrial data in the Informatics major because it counts from the number of students as well, while I hope that only 1 data will appear in informatics major

4

Here's a start on the DDL and sample data, but you're going to need to provide the other tables and data as well.

Create table #majors (id int , name varchar(2))
insert into #majors values
(1,'Informatics'),
(2,'Nursing')

Create table #students (id int , name varchar(2))
insert into #students values
(1,'Robert'),
(2,'Danny'),
(3,'Ratna')

Create table #industries (id int , name varchar(2))
insert into #industries values
(1,'Unilever'),
(2,'Amazons'),
(3,'Google')