# SQL Query Count

**URL:** <https://discourse.cakephp.org/t/sql-query-count/10808>\
**Category:** Need Help\
**Tags:** help\
**Created:** [December 10, 2022, 5:41am UTC](https://discourse.cakephp.org/t/sql-query-count/10808 "2022-12-10T05:41:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![werkudara](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/werkudara/32/1772_2.png) [@werkudara](https://discourse.cakephp.org/u/werkudara)\
**Post date:** [December 10, 2022, 5:41am UTC](https://discourse.cakephp.org/t/sql-query-count/10808/1 "2022-12-10T05:41:55Z")

</div>

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](https://canada1.discourse-cdn.com/flex029/uploads/cakephp/original/2X/4/485d2b35abc2ae48db8f5646102922a7c0ae2972.png)

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

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [December 12, 2022, 10:41pm UTC](https://discourse.cakephp.org/t/sql-query-count/10808/2 "2022-12-12T22:41:40Z")

</div>

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

```auto
$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:

```ruby
ownerid, oname, countOfPets

```

Like:

```ruby
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.

---

<div class="post-metadata">

**Author:** ![werkudara](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/werkudara/32/1772_2.png) [@werkudara](https://discourse.cakephp.org/u/werkudara)\
**Post date:** [December 15, 2022, 3:00pm UTC](https://discourse.cakephp.org/t/sql-query-count/10808/3 "2022-12-15T15:00:29Z")

</div>

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