mercredi 22 avril 2015

Adding all the values of a column if another column matches

I created a view to combine two tables( COMPANY and PAYMENTS ) like so where COMPANY comes from COMPANY and the PAYDUE and DATEDUE come from PAYMENTS

COMPANY | PAYDUE | DATEDUE

Now I need to basically sum up ALL the values in PAYDUE if their COMPANY and DATEDUE matches.

So let's say I have these 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

What I need is to add the PAYDUE of all the rows with identical COMPANY and DATEDUE so what I need the display to become is:

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 have matching COMPANY and DATEDUE. Can anyone suggest methods for this?

Also forgot to mention I was hoping to do this all in a query, but if there's no way to do this within a query I'll be happy with any solution.

FINAL WORKING QUERY

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

Aucun commentaire:

Enregistrer un commentaire