Formatting JSON data monthwise for HighCharts using MySQL

Question

There are two tables. First table shows me the number of pregnant women under one nurse. Keep in mind that there are many nurses who come under one doctor. Each pregnant woman is mapped to a single nurse and each nurse is mapped to a single doctor. So the hierarchy is

Pregnant Women-->Nurse-->Doctor .

Now, each pregnant woman has a registration date.

Second table shows me the mapping of nurses to doctors.

Now, my query should fetch the details of doctors and the count of pregnant women registered order by month(from January to December) for each doctor. So my final result should be in JSON format as applied below. This format I need it for HighCharts.

And if there has been no pregnant women for a particular month, it should return 0

[{
        name: 'Doctor ID',
        data: [7.0(Jan), 6.9(Feb), 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3,   13.9, 9.6(Dec)],

       name: 'Doctor ID 2',
        data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
    }, {
        name: 'Doctor Id 3',
         data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
    }]

I have tried the following query, but I am not able to create the structure when it comes to Months.

SELECT preg_reg_other.preg_id,nurse_dets.doctor_id,preg_reg_other.reg_date
from preg_reg_other,nurse_dets
where preg_reg_other.nurse_id = nurse_dets.nurse_id
and preg_reg_other.reg_date is not null
and nurse_dets.doctor_id= 1031
order by preg_reg_other.reg_date asc

EDIT

the two tables are as follows

table 1 : nurse_dets

nurse_id  population  doctor_id nurse_name

table 2 : preg_reg_other

preg_id  nurse_id  reg_date  

Answer 1

I would do this way:

For every doctor do a query like this:

/* get all pregrant women for a doctor, group by month and count records */
SELECT MONTH( p.reg_date ), COUNT(*) 
FROM nurse_dets n
INNER JOIN preg_reg_other p 
    ON n.nurse_id = p.nurse_id 
WHERE n.doctor_id = 1301
AND n.reg_date IS NOT NULL
GROUP BY MONTH( p.reg_date )
ORDER BY p.reg_date ASC

This will give you, for every month, the count of pregnant women associated with the doctor with id 1301

Now, you could do a query like this for every doctor and, in every iteration, build a different serie for you chart

In the end you will pass all your series array to your client and fetch the data for highcharts

EDIT

Getting the value of 0 for the months where there are no pregnant women is a litte more complicated, as the group by doesn't return any value if there isn't at least one record to count. Try this way:

SELECT months.num, COUNT( p.preg_id )
FROM 
   ( SELECT 1 AS num
     UNION ALL SELECT 2 
     UNION ALL SELECT 3 
     UNION ALL SELECT 4 
     UNION ALL SELECT 5  
     UNION ALL SELECT 6 
     UNION ALL SELECT 7  
     UNION ALL SELECT 8 
     UNION ALL SELECT 9  
     UNION ALL SELECT 10
     UNION ALL SELECT 11  
     UNION ALL SELECT 12 ) months
LEFT JOIN preg_reg_other p 
    ON months.num = MONTH ( p.reg_date )
INNER JOIN nurse_dets n
    ON n.nurse_id = p.nurse_id
WHERE n.doctor_id = 1301    
GROUP BY MONTH( months.num )
ORDER BY months.num ASC

First we manually build a table with the 12 months and "left join" with the other tables, so if there isn't any record, the final count should result in 0

I'haven't tested the query, but i hope you get the idea