Question: I have table employer with data like this.

```
+--------------+--------+
| BANDINDIVIDU | GENDER |
+--------------+--------+
| I            | M      |
| I            | F      |
| I            | M      |
| I            | M      |
| I            | F      |
| I            | M      |
| II           | M      |
| II           | M      |
| II           | M      |
+--------------+--------+

```

I tried to count the data based on BANDINDIVIDU column so the data will looks like this..

```
+--------------+--------+--------------+
| BANDINDIVIDU | GENDER |COUNT(GENDER) |
+--------------+--------+--------------+
| I            | M      | 4            |
| I            | F      | 2            |
| II           | M      | 3            |
| II           | F      | 0            |
+--------------+--------+--------------+

```

I use this sql to group the data by BANDINDIVIDU and GENDER

```
SELECT BANDINDIVIDU, GENDER from employer GROUP BY BANDINDIVIDU, GENDER;

```

but i got this for the result.

```
+--------------+--------+--------------+
| BANDINDIVIDU | GENDER |COUNT(GENDER) |
+--------------+--------+--------------+
| I            | M      | 4            |
| I            | F      | 2            |
| II           | M      | 3            |
+--------------+--------+--------------+

```

The data BANDINDIVIDU for M GENDER doesn't shown..
I already try to use right join but the data result still wrong.
I'm using mysql, and Thanks in Advance.

Answer: 
Those results are technically correct. Databases can only work with the data they are provided. Trying to group 'II' and 'F' together when there is no data to base that off of is pattern based, not data based.

My suggestion would be to create a query specifically for finding a pattern, such as 'M' and 'F'. Like this:

```
SELECT DISTINCT GENDER FROM employer

```

Just a note, setting up patterns purely in SQL can result in over complicated queries. If you are able to, I highly recommend that you do the pattern work using some kind of coding and the data retrieval using SQL.
You can also do something like this - but I think Bryan's advice about using application level code to handle the logic is right...

```
SELECT BANDINDIVIDU 
     , SUM(gender='M') M
     , SUM(gender='F') F 
  FROM my_table 
 GROUP 
    BY bandindividu;

```

And note that relation tables require PRIMARY KEYs