I will really appreciate if the community can help me with the following problem that I have not been able to solve.
The data retrieved from mysql after performing the query is in this format:
id | invdate | product | total | amountpaid | balance
----------------------------------------------------------------------
1 01/04/2015 choco 10000 2000 8000
1 01/04/2015 choco 10000 5000 3000
2 03/04/2015 apple 5000 1000 4000
2 03/04/2015 apple 5000 2000 2000
The mysql query that I have used is as follows for the above result:
SELECT tprojects.grand_total AS gtot, payments.amount_paid AS apaid, payments.balance AS bal, tprojects.CreatedDate, tprojects.IdProject, tprojects.product, tprojects.vat, tprojects.vtf, tprojects.ProjectStatus, tclients.Company FROM tprojects INNER JOIN payments ON payments.projectid = tprojects.IdProject INNER JOIN tclients ON tclients.IdClient = tprojects.client_id
So how would I make the result of the query to be the following in order to be printed for viewing purposes:
id| invdate | product | total | amountpaid | balance
1 01/04/2015 choco 10000 7000 3000
2 03/04/2015 apple 5000 3000 2000
I would really appreciate the community's help in this regard. I have been at this for the past 2 days.
Thanks in advance.
select id, invdate, product, total, sum(amountpaid), total - sum(amountpaid)
from invoices
group by id, invdate, product, total;
Note this won't work properly if an invoice has more than one product on it - but your schema isn't really setup to handle these cases very well. In fact it could be a lot better set up in general.
You need to use group by and sum Below query may help you
SELECT tprojects.IdProject,tprojects.CreatedDate,tprojects.product,tprojects.grand_total AS gtot,
SUM(payments.amount_paid) AS apaid, (tprojects.grand_total - SUM(payments.amount_paid)) AS bal
FROM tprojects
INNER JOIN payments ON payments.projectid = tprojects.IdProject
INNER JOIN tclients ON tclients.IdClient = tprojects.client_id
GROUP BY tprojects.IdProject