0

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

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
Santiago Jesus Narváez
  • 1
  • 1

1 Answers1

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
rudolfovic
  • 3,163
  • 2
  • 14
  • 38
  • I am using Sybase, the challenge here is to do it with out CASE WHEN or IF statements – Santiago Jesus Narváez Jun 20 '21 at 17:31
  • You could group by gender as well and then use window functions (partitioning by doctor order by sex) namely lag to combine both values in the same row. I don’t see why you wouldn’t just use conditionals though – rudolfovic Jun 20 '21 at 21:03
  • thanks a lot for your answer at the end I might end up using It. Yes this is very functional and solves the problem niceley. However, I should´nt use the conditionals because this is part of acourse I am attending and we have not seen those concepts yet. We should be availble to do it only with count, where and group by – Santiago Jesus Narváez Jun 21 '21 at 02:02
  • 1
    Ok so I’m this case simply group by both the doctor and gender. You will have one row with the male count and one with the female count for each doctor. If you want both number to appear in the same row that is to have exactly one row for each doctor, you need to use some kind of conditioning. – rudolfovic Jun 21 '21 at 05:54