I need to do a query from 2 tables using count function

Question

The query contains 4 columns: the full name of the doctor, the number of male patients, the number of female patients, and the total number of patients seen by that doctor.

My problem is that I dont know how to count the number of males and females

I am only suppoused to use COUNT, GROUP BY and basic DML (cant use case when)

data in the table PACIENTE

er diagram data in table medico

Answer 1

This depends on which database you are using specifically. One possible way to write this is:

SELECT
  doc_name,
  COUNT(CASE WHEN PAT_SEX = 'M' THEN 1 END) males,
  COUNT(CASE WHEN PAT_SEX = 'F' THEN 1 END) females
FROM
...

Another common syntax for this is:

COUNT(IF PAT_SEX = 'M' THEN 1 ENDIF)

Some databases support this directly:

COUNTIF(PAT_SEX = 'M')

If you would really like to avoid any kind of conditional, then you could add gender to your groups but then you will have two rows for each doctor:

SELECT
  doc_name,
  pat_sex,
  count(*)
FROM
...
GROUP BY
  doc_name,
  pat_sex