SQL Query Count

Need Help
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

I normally do joins first. Example not cakephp but will give you an idea:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

But see if you can reduce the joins to related data with eager loading and pagination. Those joins consume a lot of time when a database grows. Just suggestion.

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