mysql - SUM 一年中每个月的数据

Question

我有这样的 table

IdExpense Description TotalExpense  Dt_Expense
1            A        500        2012/03/12

我想计算特定月份的所有数据例如:

Januari February
     1500     500 

我试过这个查询，但总是出错:

    SELECT 
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 1 THEN 1) AS 'January',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 2 THEN 2) AS 'February',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 3 THEN 3) AS 'March',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 4 THEN 4) AS 'April',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 5 THEN 5) AS 'May',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 6 THEN 6) AS 'June',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 7 THEN 7) AS 'July',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 8 THEN 8) AS 'August',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 9 THEN 9) AS 'September',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 10 THEN 10) AS 'October',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 11 THEN 11) AS 'November',
  SUM(TotalExpense CASE WHEN MONTH(Dt_Expense) = 12 THEN 12) AS 'December'
FROM
    expense
WHERE
  Dt_Expense BETWEEN '2017/01/01' AND '2017/12/31'

谢谢

Answer 1

你可以改成这个

SELECT 
    SUM(CASE WHEN MONTH(Dt_Expense) = 1 THEN TotalExpense ELSE 0 END) AS January,
    SUM(CASE WHEN MONTH(Dt_Expense) = 2 THEN TotalExpense ELSE 0 END) AS February,
    SUM(CASE WHEN MONTH(Dt_Expense) = 3 THEN TotalExpense ELSE 0 END) AS March,
    SUM(CASE WHEN MONTH(Dt_Expense) = 4 THEN TotalExpense ELSE 0 END) AS April,
    SUM(CASE WHEN MONTH(Dt_Expense) = 5 THEN TotalExpense ELSE 0 END) AS May,
    SUM(CASE WHEN MONTH(Dt_Expense) = 6 THEN TotalExpense ELSE 0 END) AS June,
    SUM(CASE WHEN MONTH(Dt_Expense) = 7 THEN TotalExpense ELSE 0 END) AS July,
    SUM(CASE WHEN MONTH(Dt_Expense) = 8 THEN TotalExpense ELSE 0 END) AS August,
    SUM(CASE WHEN MONTH(Dt_Expense) = 9 THEN TotalExpense ELSE 0 END) AS September,
    SUM(CASE WHEN MONTH(Dt_Expense) = 10 THEN TotalExpense ELSE 0 END) AS October,
    SUM(CASE WHEN MONTH(Dt_Expense) = 11 THEN TotalExpense ELSE 0 END) AS November,
    SUM(CASE WHEN MONTH(Dt_Expense) = 12 THEN TotalExpense ELSE 0 END) AS December
FROM
    expense
WHERE
    Dt_Expense BETWEEN '2017/01/01' AND '2017/12/31';