CODEWITHSUNDEEP
phpmysql
lStoilov
lStoilov

Reputation: 1339

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

Upvotes: 1

Views: 490

Answers (3)

Itay Grudev
Itay Grudev

Reputation: 7424

Simply, what you need in this case is to GROUP the rows by the month column. 

SELECT month, SUM(lessons) AS lessons FROM lessons_by_month GROUP BY month

I also made an SQL Fiddle with which you can tweak around if you need to.

Here is an example output, based on the input from your question:

month    | lessons
------------------
February | 6
January  | 19
March    | 20

Upvotes: 2

fortune
fortune

Reputation: 3372

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

Upvotes: 2

colburton
colburton

Reputation: 4715

select group_concat(month), group_concat(l) from (

    SELECT month, SUM(lessons) as l FROM lessons_by_month GROUP BY month

) foo

Will give you 

|   GROUP_CONCAT(MONTH) | GROUP_CONCAT(L) |
|-----------------------|-----------------|
| February,January,June |          2,6,10 |

Upvotes: 2

Related Questions