3

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);
Cytus
  • 271
  • 1
  • 2
  • 10

1 Answers1

1

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 aggregate functions, I have never tried in Android but I do tried in an sqlitemanager and it worked fine. About this c.company = p.company, you need a way to know what payment is for what company, I assume the company table is describing the companies, and the payment table is describing the payments and has one column that specifies the company that the payment belongs to.

I re-read your question, so, from the view should be like this:

Select v.Company, sum(v.paydue), v.datedue
from CompanyPaymentView v
where
group by v.datedue, v.company
Alejandro Cumpa
  • 2,118
  • 1
  • 24
  • 45
  • I've tried using the sum function you mentioned but ran into an error. I posted in an edit, did I do something wrong? I also need a little clarification on the `c.company = p.company` area. Where do I get `p.company`, my p table doesn't have a `company` column. – Cytus Apr 21 '15 at 15:20
  • Also on a side note I read [here](http://stackoverflow.com/questions/29414170/sqlite-how-to-sum-the-values-of-column-in-gridview) that sqlite doesn't have core any function for adding columns, was this answer wrong? – Cytus Apr 21 '15 at 15:22
  • 1
    Thank you for the heads up, for some reason I wasn't notified of the edit before. Got it to work with some minor adjustments, mostly little syntax errors on my end. I'll put up the working query up in an edit. Thanks again! – Cytus Apr 22 '15 at 11:22