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
  • Is id_number not already unique? Do you have duplicate id_numbers in the table? Dec 18, 2018 at 14:48
  • @EvanCarroll, yes I have duplicate id_numbers in the table Dec 18, 2018 at 14:51

2 Answers 2


If id_number is not UNIQUE (by extension) nor a PRIMARY KEY what you want is COUNT(DISTINCT ...)

    gender, count(DISTINCT name, surname, id_number)
FROM table1
GROUP BY gender;

And you want either,

  • an index on gender, and a second index on name, surname, id_number
  • an index on gender, name, surname, id_number

No guarantee that MySQL will do something smart with them anyway, but it'll use them for the first sorting into Male/Female which will at the least reduce the DISTINCT load by ~1/2. As compare to the current answer which may double it.


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)
            , surname
            , id_number
            , gender
        FROM table1
        ) AS D
GROUP BY gender; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.