COUNT with multiple table query where I've got to display COUNT results

Question

How do I solve this one? None of my queries works and I've tried few.

I've got to return details of two doctors who ordered the most analysis including the number of analysis they have ordered. That last part of the sentence is what really drives me mad.

To work with I've got these two tables:

mysql> select * from DOCTORES;
+-----------+---------------------------+--------------+-----------+
| DNI_DOC   | NOMBRE_DOC                | ESPECIALIDAD | TELEFONO  |
+-----------+---------------------------+--------------+-----------+
| 22888444O | MATEO DÍAZ, RAMÓN         |            3 | 659876457 |
| 22909456Y | HERAS PRADO, ANTONIA      |            1 | 676234598 |
| 23456398F | GÓMEZ DAVID, ADRIÁN       |            1 | 646768454 |
| 25349857H | BURGOS CASA, CANDY        |            2 | 659758476 |
| 55776898K | RAMÓN CORONADO,LUIS       |            3 | 654364736 |
| 78988484B | CONRADO ALONSO, JOSE      |            2 | 645878745 |
| 88647389P | DOMÍNGUEZ GÓMEZ, MANUEL   |            1 | 623787343 |
+-----------+---------------------------+--------------+-----------+

and:

mysql> select * from PETICIONES;
+--------+------------+--------+-----------+-----------+
| ID_PET | FECHA_PET  | ID_ANA | DNI_PAC   | DNI_DOC   |
+--------+------------+--------+-----------+-----------+
|      1 | 2008-01-03 |      2 | 71515623A | 23456398F |
|      2 | 2008-05-10 |      2 | 33788976F | 55776898K |
|      3 | 2008-05-08 |      3 | 79876867X | 23456398F |
|      4 | 2008-05-11 |      4 | 44787345H | 55776898K |
|      5 | 2008-05-12 |      2 | 19887234W | 25349857H |
|      6 | 2008-05-05 |      4 | 22897576R | 55776898K |
|      7 | 2008-03-15 |      5 | 44787345H | 88647389P |
|      8 | 2008-03-19 |      1 | 71515623A | 23456398F |
|      9 | 2008-03-26 |      2 | 71515623A | 78988484B |
|     10 | 2008-03-15 |      2 | 19887234W | 88647389P |
|     11 | 2008-03-15 |      3 | 33788976F | 55776898K |
|     12 | 2008-03-26 |      2 | 44787345H | 23456398F |
+--------+------------+--------+-----------+-----------+

I tried this:

select 
    NOMBRE_DOC 
from 
    DOCTORES 
where 
    DNI_DOC IN (select DNI_DOC 
                from PETICIONES 
                group by ID_ANA 
                order by count(*) desc 
                limit 2) 
group by 
    DNI_DOC;

and that:

SELECT 
    DNI_DOC, ID_ANA, COUNT(ID_ANA) AS count 
FROM
    TIPOS_ANALISIS     
WHERE 
    ID_ANA = (SELECT ID_ANA 
              FROM 
                  (SELECT 
                       ID_ANA, COUNT(ID_ANA) as AnaCount 
                   FROM
                       PETICIONES     
                   GROUP BY 
                       by ID_ANA     
                   ORDER BY
                       AnaCount DESC 
                   LIMIT 1) t1) 
GROUP 
    BY ID_ANA;

and that:

select a.* from DOCTORES a
    -> where a.DNI_DOC = ( SELECT b.DNI_DOC from PETICIONES b
    -> group by d.ID_ANA
    -> order by count(ID_ANA) DESC
    -> limit 2 );

you can't even imagine how frustrating it is when you say it's a simple query... Why it isn't simple to me is a mystery (provided that I am really not a dummy).

Expected output would be something like this:

+-----------+---------------------------+--------------+-----------+
| DNI_DOC   | NOMBRE_DOC                |    ID_ANA    |  count    |
+-----------+---------------------------+--------------+-----------+
| 22888444O | MATEO DÍAZ, RAMÓN         |            3 |        6  |
+-----------+---------------------------+--------------+-----------+

1) Provide expected output based on the above sample data. 2) Provide what you have tried and what was wrong with that. This seems to be a relatively straightforward query with count, order by, and limit, therefore it would really be nice to know the details of the task. — Shadow
– Shadow, Commented Oct 5, 2016 at 10:30
Look like a group by query but I wait like @Shadow for more info. — AxelH
– AxelH, Commented Oct 5, 2016 at 10:33
including the number of analisis --> Do you mean the Count of analysis conducted by them or the ID_ANA, agree with others regarding the expected output. — dubes
– dubes, Commented Oct 5, 2016 at 10:53

Shadow · Accepted Answer · 2016-10-05 11:05:44Z

2

I believe you are severely overcomplicating this. Join the 2 tables on DNI_DOC field, group by the doctor's id and name (and any other details you may want to include in the select list), and count the number of distinct ID_ANAs.

SELECT d.DNI_DOC, d.NOMBRE_DOC, COUNT(distinct ID_ANA) AS count
FROM DOCTORES d
INNER JOIN PETICIONES t ON d.DNI_DOC=t.DNI_DOC
GROUP BY d.DNI_DOC, d.NOMBRE_DOC
ORDER BY COUNT(distinct ID_ANA) DESC
LIMIT 2

If you need the total number of peticiones per doctor, then remove the distinct from the count.

edited Oct 5, 2016 at 11:05

answered Oct 5, 2016 at 10:59

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AxelH Over a year ago

DIdn't saw that table TIPOS_ANALISIS the first time... but that would be my way too

CNB Over a year ago

Thanks, overcomplicating seems to be my speciality and probably my main issue with getting it right.

AxelH · Accepted Answer · 2016-10-05 11:23:45Z

1

Here is what I would do :

select d.*, count(p.ID_PET) as TOTAL_PETICIONES from DOCTORES d
    inner join PETICIONES p on p.DNI_DOC = d.DNI_DOC
    group by d.DNI_DOC
    order by count(ID_ANA) DESC
    limit 2

This is not tested and write here so please correct me for typos ;) Using a join will provide a nice output. Grouping by DOCTORES to be able to count the PETICIONES per DOCtORES

EDIT : Something like that. The output won't be good because I have issues to understand the column content.

edited Oct 5, 2016 at 11:23

answered Oct 5, 2016 at 10:54

AxelH

14.6k2 gold badges27 silver badges56 bronze badges

2 Comments

CNB Over a year ago

I did't think of that, of course I was going about it from a wrong angle! Typical... Works as a charm. Thank you!

CNB Over a year ago

the output is great.

Collectives™ on Stack Overflow

COUNT with multiple table query where I've got to display COUNT results

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related