Mysql每年每月获取行数

Question

Im trying to find the sum of cost for each month in each year.

I have the below table 'logs'

date               |    cost
---------------------------------
Wed, 01 Aug 2016   |    45
Tue, 15 Aug 2016   |    52
Mon, 31 Aug 2016   |    23
Thu, 05 Sep 2016   |    9
Sat, 10 Sep 2016   |    33
Mon, 12 Feb 2017   |    8
Tue, 31 Feb 2017   |    0
Wed, 31 Mar 2017   |    100
Fri, 31 Mar 2017   |    35
Thu, 31 Mar 2017   |    45

This is what im trying to achive,

2017

Feb 8+0

Mar 100+35+45

---

2016

Aug 45+52+23

Sep 9+33

---

Currently im doing this,

$sqly = "SELECT DISTINCT RIGHT(date,4) as year FROM logs ORDER BY id DESC;";
$resy = mysql_query($sqly);
while($rowy = mysql_fetch_array($resy))
{
   echo $rowy['year'];
}

Result:

2017

2016

I dont know how to proceed further to get the months on each year and sum the total for each month.

Answer 1

This query should work:

SELECT YEAR(STR_TO_DATE(date, '%W, %d %M %Y')) AS year,
       MONTH(STR_TO_DATE(date, '%W, %d %M %Y')) AS month, 
       SUM(cost) 
FROM logs 
GROUP BY year, month;