查找列月份的总和Mysql [英] Finding sum of column month wise Mysql

问题描述

请Hepl ...

我有这样的表格。

 ID日期金额
 1 19/04 / 2015 100 
 1 20/04/2015 1000 
 1 19/05/2015 1000 
 2 19/04/2015 2000 
 2 23/04/2015 1500 
 2 24/04/2015 2500 
 2 05/05/2015 3000 
 2 19/05/2015 1500 
 1 20/05/2015 2000 
 1 23/05/2015 3000 
 1 23/07/2015 3000 

i有开始日期和结束日期，如04/04/2014和04 / 03/2015

如何编写查询以获取每个月的日期范围总金额每个ID

之类的..

 ID日期总计
 1 04/2014 1100 
 1 05/2014 6000 
 1 06/2014 0 
 1 07/2014 3000 
 1 08 / 2014 0 
 1 09/2014 0 
 1 10/2014 0 
 1 11/2014 0 
 1 12/2014 0 
 1 01/2015 0 
 1 02 / 2015 0 
 1 03/2015 0 
 2 04/2014 6000 
 2 05/2014 4500 
 2 06/2014 0 
 2 07/2014 0 
 2 08/2014 0 
 2 09/2014 0 
 2 10/2014 0 
 2 11/2014 0 
 2 12/2014 0 
 2 01 / 2015 0 
 2 02/2015 0 
 2 03/2015 0 

请帮助..如何检索像这样的记录..



先谢谢..

解决方案

  SELECT  
 ID 
 CAST（CalMonth  AS   CHAR （ 2 ））+ '  /' + CAST（CalYear  AS   CHAR （ 4 ）） AS  日期，
总计
  FROM （
  SELECT  
 ID，
 MONTH（日期） AS  CalMonth，
年份（日期）  AS  CalYear，
 SUM（总计） AS 总计
  FROM  [TableName] 
  WHERE  
 日期  BETWEEN  '  04/04/2014'  AND  '  04/03/2015' 
  GROUP   BY  
 ID，
 MONTH（日期），
年份（日期））

0 92 15:58:48 SELECT

loan_id，

CAST（Calday AS char（2））+' - '+ CAST（CalMonth AS） CHAR（2））+' - '+ CAST（CalYear AS CHAR（4））AS disbursement_date，

总计

FROM（

SELECT

loan_id，

DAY（disbursement_date）AS Calday，

MONTH（disbursement_date）AS CalMonth，

年（ disbursement_date）AS CalYear，

SUM（paid_amount）AS总计

来自source.finance_formated

WHERE

disbursement_date BETWEEN '04 -04-2015'和'04 -07-2015'

GROUP BY

loan_id，

DAY（disbursement_date），

MONTH（disbursement_date），

年（disbursement_date））

LIMIT 0,1000错误代码：1248。每个派生表必须有自己的别名0.094秒

Please Hepl...
I have table like this.

ID    Date             Amount   
1     19/04/2015       100
1     20/04/2015       1000
1     19/05/2015       1000
2     19/04/2015       2000
2     23/04/2015       1500
2     24/04/2015       2500
2     05/05/2015       3000
2     19/05/2015       1500
1     20/05/2015       2000
1     23/05/2015       3000
1     23/07/2015       3000 

i have start date and end date like 04/04/2014 and 04/03/2015
How to write query to get each month between date range total amount for each Id
like..

ID   Date           Total
1    04/2014     1100
1    05/2014     6000
1    06/2014     0
1    07/2014     3000
1    08/2014     0
1    09/2014     0
1    10/2014     0
1    11/2014     0
1    12/2014     0
1    01/2015     0
1    02/2015     0
1    03/2015     0
2    04/2014     6000
2    05/2014     4500
2    06/2014     0
2    07/2014     0
2    08/2014     0
2    09/2014     0
2    10/2014     0
2    11/2014     0
2    12/2014     0
2    01/2015     0
2    02/2015     0
2    03/2015     0

Please help.. how to retrieve records like this..

Thanks in Advance..

解决方案

SELECT
	ID
	CAST(CalMonth AS CHAR(2)) + '/' + CAST(CalYear AS CHAR(4)) AS Date,
	Total
FROM (
	SELECT
	    ID,
	    MONTH(Date) AS CalMonth,
	    YEAR(Date) AS CalYear,
	    SUM(Total) AS Total
	FROM [TableName]
        WHERE
            Date BETWEEN '04/04/2014' AND '04/03/2015'
	GROUP BY
	    ID,
	    MONTH(Date),
	    YEAR(Date))

0 92 15:58:48 SELECT
loan_id,
CAST(Calday AS char(2)) + '-' + CAST(CalMonth AS CHAR(2)) + '-' + CAST(CalYear AS CHAR(4)) AS disbursement_date,
Total
FROM (
SELECT
loan_id,
DAY (disbursement_date) AS Calday,
MONTH(disbursement_date) AS CalMonth,
YEAR(disbursement_date) AS CalYear,
SUM(paid_amount) AS Total
FROM source.finance_formated
WHERE
disbursement_date BETWEEN '04-04-2015' AND '04-07-2015'
GROUP BY
loan_id,
DAY(disbursement_date),
MONTH(disbursement_date),
YEAR(disbursement_date))
LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.094 sec

这篇关于查找列月份的总和Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！