How to count male or female for each country ?

@hewaa share me your code ..and tell me the name of your coloumn in which your data ( male and female) are storing in your database.

@hewaa and which column is not found , tell me the name ..

@hewaa shivamyaadv4046@gmail.com share your database screenshot to me on my gmail

@hewaa try this and share your screenshot to me on my email address

    $countries = Cuntry::query();

    // Search

    if (!empty($this->term)&& $this->term != null)
    {
        $countries = $countries->search(trim($this->term));
    }

    // * Trashed

    if ($this->trashed){
        $countries = $countries->onlyTrashed();
    }else{
        $countries = $countries->withCount('city');
        $countries = $countries->withCount('employ');
        $countries = $countries->where('gender_id', 1)->count(); //for male 
		$countries = $countries->where('gender_id', 2)->count(); //for female

    }

@Shivamyadav Ok

@hewaa send the screenshot as soon as possible dude? sorry my bad .. my original email address is shivamyadav4046@gmail.com

@hewaa yea this is my email address shivamyadav4046@gmai.com . I have misspelled typed it

@hewaa but I have not received yet , send me your email address

@hewaa have that you seen there it is showing that ...

select count() from generals where....

that means it is selecting the general table and searching that column gender_id in that table.

@hewaa have u written this line word Cuntry::query() model name properly ;

@hewaa and were is gender_id in which table?

@Shivamyadav Employ tbl

@hewaa okay has You then created any relation with the employ to the generals or your country table?

@Shivamyadav no just in model General said hasmany gender and on Gender belongto General but in migration table there is not have any relashionship

@hewaa That means in tables there is no any foreign Key ?

@Shivamyadav Yes there is not have in General model Just i have Name and slug

@hewaa but this will work for you ,just you have to set the foreign id and by using the relationship method you have to just simply call the the method in the conntroller..

    $countries = Cuntry::query();

    // Search

    if (!empty($this->term)&& $this->term != null)
    {
        $countries = $countries->search(trim($this->term));
    }

    // * Trashed

    if ($this->trashed){
        $countries = $countries->onlyTrashed();
    }else{
        $countries = $countries->withCount('city');
        $countries = $countries->withCount('employ');
        $countries = $countries->where('gender_id', 1)->count(); //for male 
		$countries = $countries->where('gender_id', 2)->count(); //for female
		//you have to just to do it like this.
   		$countries = $countries->your relationship method() here and everyting will be same->where('gender_id', 1)->count(); //for male 

    }

@hewaa From my knowledge there is no logic for fetching the data from one table to another table with using slug slug is used for route model binding...

@hewaa send me your table screenshot and model code screenshot on my email..

@Shivamyadav $generals = $generals->with('employ')->where('gender_id', 1)->count();

but stall say Unknown column 'gender_id'

ok i send you

@hewaa now try this code

    $countries = Cuntry::query();

    // Search

    if (!empty($this->term)&& $this->term != null)
    {
        $countries = $countries->search(trim($this->term));
    }

    // * Trashed

    if ($this->trashed){
        $countries = $countries->onlyTrashed();
    }else{
        $countries = $countries->withCount('city');
        $countries = $countries->withCount('employ');
        $countries = $countries->police()->where('gender_id', 1)->count();
		$countries = $countries->police()->where('gender_id', 2)->count();

    }

@Shivamyadav

Call to undefined method Illuminate\Database\Eloquent\Builder::police()

@hewaa try this import it on the top of the controller use Illuminate\Database\Eloquent\Police;

    $countries = Cuntry::query();

    // Search

    if (!empty($this->term)&& $this->term != null)
    {
        $countries = $countries->search(trim($this->term));
    }

    // * Trashed

    if ($this->trashed){
        $countries = $countries->onlyTrashed();
    }else{
        $countries = $countries->withCount('city');
        $countries = $countries->withCount('employ');
        $countries = $countries->police()->where('gender_id', 1)->count();
		$countries = $countries->police()->where('gender_id', 2)->count();

    }

use this code , I have updated it now and don't forget to import the class on the top of the controller!

@shivamyadav

Call to undefined method Illuminate\Database\Eloquent\Builder::police()

but this use App\Models\Police; like is not using

public function getItem(){

    $generals = General::query();

    // * Search

    if (!empty($this->term)&& $this->term != null){
        $generals = $generals->search(trim($this->term));
    }
    // * Trashed
    if ($this->trashed){
        $generals = $generals->onlyTrashed();
    }else{
        $generals = $generals->withCount('user');
        $generals = $generals->withCount('directorate');
        $generals = $generals->withCount('department');
        $generals = $generals->withCount('police');
        $generals = $generals->police()->where('gender_id', 2)->count();

    }

@hewaa replace it use App\Models\Police; with this

use Illuminate\Database\Eloquent\Police;

@jlrdw please how do that can you give me a solution

i have 3 model (department -hasMany->Employ-belongsTo->Gender) i want count male for each department Employ column (department_id , gender_id ) i tried this code not work ( $department = $department->employ()->where('gender_id', 2)->count();)

@hewaa you can first groupby department and another groupby the count of gender. A decent tutorial is: https://www.mysqltutorial.org/mysql-group-by.aspx

Then go over count usage.

@jlrdw really i'm confused it's hard to understand just write the code i was change to my code

@hewaa I don't have your data, but something like this, here I use ownerid instead.

SELECT dc_pets.ownerid, dc_pets.sex,
COUNT(dc_pets.sex) AS  count_gender
FROM dc_pets
WHERE dc_pets.ownerid >= 1
GROUP BY dc_pets.ownerid, dc_pets.sex;

query result

ownerid	sex	 count_gender
1 	M 	11
1 	f 	2
2 	M 	12
2 	F 	1
3 	M 	1
3 	F 	1
4 	F 	1
4 	M 	3
5 	m 	1
5 	F 	4

So the owner with the id of 1 has 11 male pets and 2 female pets.

To get the exact format you need, just use a little trial and error.

Edit:

Also experiment with case and sum. A little trial and error is usually all it takes.

@rodrigo.pedra thast i want thansk solved <3