largan largan - 3 months ago 8
MySQL Question

Sum sql values based on distinct values from another column and put the result in array

I am trying to to create an array based on the results from sum values based on the distinct values from another column

I have these two columns Month and Lessons. In the column month is the month name when the lessons were given (January, February...). In the column lessons I have values like (12,7,6,9,11, etc.)

Example Values

Month |lessons
------------------
January |12
January |7
February |6
March |9
March |11


What I want to achieve is to sum all lessons for every month and then put the values in an array for example ($month_values = January Sum, February Sum, etc).

Example Result

$month_values = 19,6,20;


I have managed to get the months with this query

$sth = $db->prepare("SELECT DISTINCT month FROM acc where month!=''");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
$months = json_encode($result);


But I am struggling to get the values for each month

I have searched here to see if anyone faced the same challenge, but couldn't find anything that can help me

Any suggestion how to alter the query above and to put the lessons values in a array?

Thanks

Answer
SELECT month, SUM(lessons) 
FROM lessons_by_month 
GROUP BY month 
ORDER BY MONTH(STR_TO_DATE(month,'%M')) ASC

The above query will output you sum of lessons in the calendar month order.

SQLFIDDLE DEMO

Comments