MYSQL-每年获取一行，每个月总计 [英] MYSQL - get a row for each year, with total sum for each month

问题描述

我有一张购买交易表.每笔交易都有时间戳和购买金额(以美元为单位).

I have a table of transactions for purchases. Each transaction has a timestamp and purchase amount (in USD).

我正在尝试从中创建一些统计信息.我想为每年提取一行，其中包含一年中每个月的总和. (我希望没有交易的月份总计为0-不省略.)

I'm trying to create some stats from this. I'd like to extract a row for each year that contains the sum for each month in the year. (I'd like months with no transaction to sum to 0 - not omitted.)

我知道我可以对所有内容进行简单的SELECT并在PHP中进行处理，但是我想知道是否有可能让MySQL按我的意愿进行工作并提取数据?

I know I could just do a plain SELECT of everything and process it in PHP, but I was wondering if it was at all possible to make MySQL do the work and extract the data like I want it?

我想看到的是这样的行:

What I'd like to see is rows like:

Year, Total_Jan, Total_Feb, ... Total_Dec, Total_Year

我能够获得每年的总数，但是我不知道如何将每月的总数计入同一行.

I am able to get the total per year, but I can't work out how to get the total per month into the same row.

SELECT
  YEAR(dt) as the_year,
  SUM(mc_gross) AS sum_total
FROM
  transactions
GROUP BY
  the_year

推荐答案

SELECT
  YEAR(dt) as the_year,
  SUM(CASE WHEN MONTH(dt) = 1 THEN mc_gross ELSE 0 END) AS Total_Jan,
  SUM(CASE WHEN MONTH(dt) = 2 THEN mc_gross ELSE 0 END) AS Total_Feb,
  ...
  SUM(CASE WHEN MONTH(dt) = 12 THEN mc_gross ELSE 0 END) AS Total_Dec
FROM
  transactions
GROUP BY
  the_year;

这篇关于MYSQL-每年获取一行，每个月总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！