Adding all column values if another column matches
I created the idea to combine the two tables ( COMPANY
and PAYMENTS
), for example, which COMPANY
is derived from COMPANY
, and PAYDUE
, and DATEDUE
- byPAYMENTS
COMPANY | PAYDUE | DATEDUE
Now I need to basically sum ALL the values in PAYDUE
if they are the same COMPANY
and DATEDUE
.
So, let's say I have the following entries:
COMPANY | PAYDUE | DATEDUE
Comp 1 | 8000 | 4/30/2015
Comp 1 | 7000 | 5/15/2015
Comp 1 | 6000 | 4/30/2015
Comp 1 | 5000 | 5/15/2015
Comp 2 | 4000 | 4/30/2015
Comp 2 | 3000 | 5/15/2015
Comp 2 | 2000 | 4/30/2015
Comp 2 | 1000 | 5/15/2015
I need to add PAYDUE
all lines with the same COMPANY
and DATEDUE
so I need the display to become:
COMPANY | PAYDUE | DATEDUE
Comp 1 | 14000 | 4/30/2015 <- from- 8000+6000
Comp 1 | 12000 | 5/15/2015 <- from- 7000+5000
Comp 2 | 6000 | 4/30/2015 <- from- 4000+2000
Comp 2 | 4000 | 5/15/2015 <- from- 3000+1000
I don't know how to add the ones that match COMPANY
and DATEDUE
. Can anyone suggest methods for this?
Also forgot to mention that I was hoping to do all of this in a request, but if there is no way to do it in this question, I would be happy with any solution.
FINAL WORKING QUESTION
Cursor cur = db.rawQuery("SELECT " + " _id, " + colCompClass + "," + colName + ", SUM(" + colPayDue + ") AS " + colPayDue + "," + colDateDue + " FROM " + viewComps + " WHERE " + colDateDue + "=" + "( SELECT MIN (" + colDateDue + ") FROM " + viewComps + " WHERE " + colDateDue + ">=?)" + " GROUP BY " + colDateDue + "," + colCompClass, params);
source to share
I think this query should do it:
Select c.Company, sum(p.paydue), p.datedue
from company c, payments p
where
c.company = p.company
group by p.datedue, c.company
UPDATE:
According to the documentation sqlite has aggregated functions, I have never tried in Android, but I tried in sqlitemanager and it worked fine. About this c.company = p.company
you need a way to find out which payment is for which company, I suppose the company table describes the companies and the payments table describes the payments and has one column that states the company that the payment belongs to.
I am re-reading your question, so from the view it should be something like this:
Select v.Company, sum(v.paydue), v.datedue
from CompanyPaymentView v
where
group by v.datedue, v.company
source to share