用于汇总列数(月)的 mySQL 查询 [英] mySQL Query for summing amount in columns (months)

问题描述

我有以下格式的数据:

date (mm/dd/yyyy), desc (detail desc), category (a,b,c), tran_type(借方，贷方)，花费的金额(金额).

date (mm/dd/yyyy), desc (detail desc), category (a,b,c), tran_type (debit, credit), amount spent (amounts).

我想获取以下格式的数据:

I would like to get the data in the following format:

category  ||  tran_type   ||   Jan_total  ||  feb_total  ||  mar_total  
A         ||  debit       ||   $101       ||  $201       ||  $302  
A         ||  credit      ||   $500       ||  $600       ||  $200  

推荐答案

此查询应为您提供所需的结果.

This query should give you result required.

SELECT category
  ,tran_type
  ,SUM(IF(month(date) = 1,ABS(amount),0)) as jan_total
  ,SUM(IF(month(date) = 2,ABS(amount),0)) as feb_total
  ,SUM(IF(month(date) = 3,ABS(amount),0)) as mar_total
  ,SUM(IF(month(date) = 4,ABS(amount),0)) as apr_total
  ,SUM(IF(month(date) = 5,ABS(amount),0)) as may_total
  ,SUM(IF(month(date) = 6,ABS(amount),0)) as jun_total
  ,SUM(IF(month(date) = 7,ABS(amount),0)) as jul_total
  ,SUM(IF(month(date) = 8,ABS(amount),0)) as aug_total
  ,SUM(IF(month(date) = 9,ABS(amount),0)) as sep_total
  ,SUM(IF(month(date) = 10,ABS(amount),0)) as okt_total
  ,SUM(IF(month(date) = 11,ABS(amount),0)) as nov_total
  ,SUM(IF(month(date) = 12,ABS(amount),0)) as dec_total
 FROM transactions
 WHERE YEAR(date) = '2011'
 GROUP BY category, tran_type

如果您不想遇到麻烦，请不要忘记过滤年份.

Don't forget to filter on year if you don't want to run in to trouble.

这篇关于用于汇总列数(月)的 mySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！