Mysql每年每月获取行数

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.

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;

Try this query.

SELECT YEAR(date) AS year, MONTH(date) AS month, COUNT(DISTINCT id) FROM logs GROUP BY year, month

You can use below code to find your answer, Hope this will help you

<?php

$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'];

   $sqly2 = "select SUBSTRING(date, 9, 3) as month_name, Group_concat(cost,' + ') as total_cost from logs where RIGHT(date,4) = '".$rowy['year']."' GROUP BY SUBSTRING(date, 9, 3)";
    $resy2 = mysql_query($sqly2);

    while($rowy2 = mysql_fetch_array($resy2))
{
   echo $rowy2['month_name'] ." ".$rowy2['total_cost'] ;

}
}