Finding sum of column month wise Mysql

Question

0.00/5 (No votes)

See more:

Please Hepl...
I have table like this.

HTML

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..

HTML

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..

Posted 21-May-15 8:32am

AshwinGanvr

Updated 17-Nov-17 0:06am

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

virusstorm · Answer 1 · 2015-05-21T09:43:00

SQL

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))

Member 13526568 · Answer 2 · 2017-11-17T00:06:00

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

Finding sum of column month wise Mysql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Finding sum of column month wise Mysql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us