選擇查詢每月和每年的組金額（MySql）

我有一個帶有列的表名“transactionshistory”

我想創建一個選擇查詢，其中 SUM 每月和每年的金額

例如

Total_Amount | MONTH | YEAR
  100       |  01   | 2018
  110       |  02   | 2018

SELECT SUM(amount) Total_Amount, 
      MONTH(transactiondate) `MONTH`, 
      YEAR(transactiondate) `YEAR`
FROM transactionshistory
GROUP BY MONTH(transactiondate), YEAR(transactiondate)

感謝您的及時回复Akina。

MONTH | INCOME | EXPENSES | DIFFERENCE

其中 INCOME 表示所有正值的總和，EXPENSES 表示每月所有負值的總和

我試過下面的

SELECT  MONTH(transactiondate) `MONTH`, 
  YEAR(transactiondate) `YEAR`,
   SUM(CASE when amount <0 then amount END) EXPENSES ,
  SUM(CASE when amount >=0 then amount END) INCOME ,
  SUM(amount) Total_Amount 
FROM transactionshistory
GROUP BY MONTH(transactiondate), YEAR(transactiondate) order by MONTH desc

引用自：https://dba.stackexchange.com/questions/256782