How can I count distinct columns inside a group? I have the following query:

SELECT DISTINCT name, surname, id_number, gender FROM table1;


How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery? If so, how do I write the subquery?

Note in my table there are duplicate entries for id_number

Sample Data:

-------------------------------------
Name    | Surname   |ID_No   |Gender|
-------------------------------------
John       Doe        1234A6    M
Jane       Doe        2553B7    F
Steve      Jobs       2636A8    M


Expected Output

--------------
  M   |   F  |
---------------
  2       1
I'm pretty sure that this will work for you.  I don't have a MySQL instance available to me to check my syntax, but this should get you close.

SELECT gender, COUNT(id_number)
FROM (  SELECT DISTINCT
            name
            , surname
            , id_number
            , gender
        FROM table1
        ) AS D
GROUP BY gender;