I need to show how many patients there was, foreach medicine within months on my chart by months in current year. Currently my query gives me the count how many patients were total, not per medicine and I don't know how to fix that.
Current query:
SELECT `brands`.`name_et` AS `brand_name`,
`patients`.`created_at`,
count(*) AS count,
monthname(patients.created_at) AS month,
year(patients.created_at) AS year
FROM `patients`
INNER JOIN `brands`
ON `brands`.`id` = `patients`.`brand_id`
INNER JOIN `brand_presentations`
ON `brand_presentations`.`brand_id` = `brands`.`id`
INNER JOIN `dosage_forms`
WHERE `brand_presentations`.`manufacturer_id` = ?
GROUP BY `month`,
`year`,
`brand_name`
ORDER BY `year` ASC
This is my current json response
[
{
"brand_name":"med 1",
"created_at":"2018-05-15 07:20:03",
"count":1,
"month":"May",
"year":2018
},
{
"brand_name":"med 2",
"created_at":"2018-07-03 14:57:42",
"count":1,
"month":"July",
"year":2018
},
{
"brand_name":"med 3",
"created_at":"2018-07-03 14:57:42",
"count":2,
"month":"July",
"year":2018
},
{
"brand_name":"med 4",
"created_at":"2018-07-03 15:00:07",
"count":3,
"month":"July",
"year":2018
}
]
And this is my expected json response.
[
{
"May":[
{
"brand_name":"Med 1",
"count":1,
"year":2018
},
{
"brand_name":"Med 3",
"count":3,
"year":2018
},
{
"brand_name":"Med 2",
"count":4,
"year":2018
}
]
}
]
Image of the desired result (created by hard coding)
Would it be better to generate desired json in PHP or can it be done within mySql?
