mardi 4 août 2015

Mysql select multiple count giving wrong values

I'm trying to find a patient's appointments and messages count. My table records are like below 3 table patient, appointments and messages
Patient table

pid     fname   lname
1      john     sid
2      rother   ford
3      megan    rough
4      louis    kane

appointments table

id  pid     appointment_date
1   1   2015-08-04
2   2   2015-08-05
3   1   2015-08-06
4   1   2015-08-07
5   3   2015-08-07
6   2   2015-08-08
7   4   2015-08-13
8   1   2015-08-12

Messages table

id  pid     description     message_date
1   2        join           2015-08-04
2   2        update         2015-08-05
3   3        join           2015-08-05
4   4        update         2015-08-10
5   3        test           2015-08-07

So if write query to find counts i'm getting wrong values

SELECT pd.fname,pd.lname , pd.pid, COUNT( a.id ) AS app_cnt, COUNT(   m.id   ) AS mes_cnt
FROM patient pd
LEFT OUTER JOIN appointments a   ON a.pid = pd.pid
LEFT OUTER JOIN messages m ON m.pid = pd.pid
GROUP BY pd.pid
ORDER BY pd.pid  

fname   lname   pid     app_cnt     mes_cnt
john    sid     1         4            0
rother  ford    2         4            4
megan   rough   3         2            2
louis   kane    4         1            1

Here pid 1 have 4 appointments and 0 messages, pid 2 have 2 appointments and 2 messages but getting wrong values.

Can someone please help to resolve this issue. I'm not interested in writing sub queries for this.

Functionality looks simple but I'm really facing problem for writing query.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire