1

I honestly had no idea how to name this question properly..

I have a sqlite db with these columns.

private final String createDb = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "
        + C_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + DATE + " text, "
        + SUBCAT + " text, "
        + ITEM + " text, "
        + PRICE + " integer, "
        + QUANTITY + " integer, "
        + WEIGHT + " integer, "
        + VOLUME + " integer, "
        + SALE + " text, "
        + STORE + " text, "
        + EXTRA + " text) ";

and was trying use ViewBinder to call a SQL query like the one below.

public String avgPrice() {
        ourHelper = new DbHelper(ourContext);
        db = ourHelper.getReadableDatabase(); 
        String sql = "SELECT AVG(PRICE) as avgprice, ITEM FROM " + TABLE_NAME + " GROUP BY " + ITEM;
        Cursor d = db.rawQuery(sql, null);

I originally had a ListView with an XML that contains a textview for each column. That just pulls each column 1:1 off the db. That was easy with an adapter. I am trying to use ViewBinder to detect the avg price for each entered item (if there are duplicate instances e.g. different date) and have that show up for the item.

Essentially I want the ListView to show only each item once (with avg price) and when you select it, you can view each stored instance (like a history).

What would be the easiest way to do this from where I'm at? Assuming that the ViewBinder method (or the query) can be used above, how do I extract the ITEM and AVG(PRICE) to put in the appropriate views only once to achieve my "history" function?

Clam
  • 935
  • 1
  • 12
  • 24
  • RED below is suggesting using UNIQUE on the ITEM. Which I think makes sense. – Clam Jan 30 '14 at 13:09
  • If I made a avg price column, can I use a similar query to what I had to put in the avg price? How would I put that calculation in? – Clam Jan 30 '14 at 13:12
  • The unique constraint doesn't help you. The idea of the new average column was to use sqlite triggers to update the column when you add a new row(with a new price) or when you update the price to recalculate the average(based on the new price). – user Jan 30 '14 at 15:56
  • I can understand how to do that if I wanted to just get the average price of every item in the entire table, but how would I do it to only calculate for each unique item. e.g. all apples, all oranges etc. – Clam Feb 01 '14 at 12:39
  • I think you can actually do that with sqlite by using the right WHERE clauses in the trigger but I'm not a sqlite expert so I'm not sure what to say. – user Feb 01 '14 at 13:39

1 Answers1

0

Add the following to the creation string of your database:

+ "UNIQUE(" + COLUMN_NAME + ") ON CONFLICT REPLACE);";

That way you will have whatever column name you want in there to be unique, duplicates will not be allowed and will be replaced if entered into the database twice. I assume one of the columns you have can be unique. Like an item_id for example, so then only one of that item can be added at a time, it will show the most recent with ON CONFLICT REPLACE.

RED_
  • 2,997
  • 4
  • 40
  • 59
  • Using unique on something like my ITEM column to ensure that only one to be entered. What happens to the other data then? – Clam Jan 30 '14 at 13:07
  • What do you mean? They will get added as normal, but based on the unique identifier. – RED_ Jan 31 '14 at 09:28
  • Yeah I think i'm not quite understanding how it functions. If the item is unique (only 1 value of that type of allowed), it would seem that the other column data would be overwritten? – Clam Jan 31 '14 at 14:43
  • Yes the old data will be overwritten. If you attempt to add in the same values twice then the original value will be replaced. – RED_ Jan 31 '14 at 14:55
  • that would be a problem then because I need the other data to stay in for the "historic record" portion of the app... So I don't think this method would work.. – Clam Feb 01 '14 at 12:30