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? – Evan Carroll Dec 18 '18 at 14:48
  • @EvanCarroll, yes I have duplicate id_numbers in the table – Lodza Wa Mampela Dec 18 '18 at 14:51

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.

| improve this answer | |

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; 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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