CODEWITHSUNDEEP
mysqldatabasedatetimegroup-bysum
Clorae
Clorae

Reputation: 73

how to sum amount based on 2 columns that are both dates in mysql

i have a tbl_remit where i need to get the last remittance.

I'm developing as system wherein I need to get the potential collection of each Employer using the Employer's last remittance x 12. Ideally, Employers should remit once every month. But there are cases where an Employer remits again for the same month for the additional employee that is newly hired. The Mysql Statement that I used was this. 

SELECT Employer, MAX(AP_From) as AP_From, 
                MAX(AP_To) as AP_To, 
                MAX(Amount) as Last_Remittance, 
                (MAX(Amount) *12) AS LastRemit_x12
FROM view_remit
GROUP BY PEN

Result

|RemitNo.|  Employer  |  ap_from  |   ap_to   | amount |
|   1    |      1     |2016-01-01 |2016-01-31 |  2000  |
|   2    |      1     |2016-02-01 |2016-02-28 |  2000  |
|   3    |      1     |2016-03-01 |2016-03-31 |  2000  |
|   4    |      1     |2016-03-01 |2016-03-31 |   400  |

By doing that statement, i ended up getting the wrong potential collection.

What I've got:
400 - Last_Remittance
4800 - LastRemit_x12 (potential collection)

What I need to get:
2400 - Last_Remittance
28800 - LastRemit_x12 (potential collection)

Any help is greatly appreciated. I don't have a team in this project. this may be a novice question to some but to me it's really a complex puzzle. thank you in advance.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You want to filter the data for the last time period. So, think where rather than group by. Then, you want to aggregate by employer.

Here is one method:

SELECT Employer, MAX(AP_From) as AP_From, MAX(AP_To) as AP_To,
       SUM(Amount) as Last_Remittance,
       (SUM(Amount) * 12) AS LastRemit_x12
FROM view_remit vr
WHERE vr.ap_from = (SELECT MAX(vr2.ap_from)
                    FROM view_remit vr2
                    WHERE vr2.Employer = vr.Employer
                   )
GROUP BY Employer;

EDIT:

For performance, you want an index on view_remit(Employer, ap_from). Of course, that assumes that view_remit is really a table . . . which may be unlikely.

If you want to improve performance, you'll need to understand the view.

Upvotes: 1

Related Questions