How to get population of gender in a country

How can I get population of gender in a country. gender may be male or female.

public function getTotalLocationOfUsers()
{
    return Profile::selectRaw('nationality, gender, COUNT(*) as count')
        ->groupBy('nationality')
        ->groupBy('gender')
        ->where('user_id', '!=', null)
        ->get();

}

The above code return this

 "data": [
    {
        "nationality": "Nigeria",
        "gender": "female",
        "count": 1
    },
    {
        "nationality": "Spain",
        "gender": "female",
        "count": 4
    },
    {
        "nationality": "Spain",
        "gender": "male",
        "count": 1
    }
    {
        "nationality": "USA",
        "gender": "female",
        "count": 3
    }
]

When you look at 'Spain' they have 1 male and 4 female, and the population is 5.

How can I write the query to get the population?

MichalOravec

5 years ago

Best Answer

Level 75

This should work

public function getTotalLocationOfUsers()
{
    return Profile::selectRaw('nationality, SUM(IF(gender = "male", 1, 0)) as males, SUM(IF(gender = "female", 1, 0)) as females, COUNT(*) as population')
        ->groupBy('nationality')
        ->where('user_id', '!=', null)
        ->get();
}

1 like

Please or to participate in this conversation.