CODEWITHSUNDEEP
androidsqliteandroid-sqlite
John Mark Delos Reyes
John Mark Delos Reyes

Reputation: 35

How to get the sum of different table but the same column names

I have 2 questions but they are almost related.

1st question:

I have 4 different tables named breakfast,lunch,dinner and others but they have the same column names like Fat,Calories and Iron.

What is the best possible way to add them and show them on a Textview as one for each (Fat,Calories and Iron)

Like for example 

Total Fat is (111)
Total Calories is (222)
Total Iron is (333)

2nd question

How about if i want to all display data of columns of different tables. How can i display all the values of column fat from different tables in sqlite?

For example, I have this on my sqlite database

breakfast table    lunch table        dinner table       others table 
  Fat column       Fat column          Fat column         Fat column
   1                  3                    6                  9
   2                  4                    7                  10
   3                  5                    8                  11

and I want to show that on textview something like this

Fats
1
2
3
4
5
6
7
8
9
10
11

Upvotes: 0

Views: 104

Answers (2)

OneCricketeer
OneCricketeer

Reputation: 191691

I have 4 different tables named breakfast,lunch,dinner and others but they have the same column names like Fat,Calories and Iron

Having different tables with the same columns should be your primary problem, not how to combine separate tables. (Because JOIN's and cross-table scans are expensive)

You should rather do this 

CREATE TABLE IF NOT EXISTS meals (
  _id PRIMARY KEY AUTOINCREMENT,
  meal_time VARCHAR(20), 
  fat INTEGER, 
  calories INTEGER, 
  iron INTEGER,
  time_eaten DEFAULT current_timestamp
)

Ref on default values - Can I create a datetime column with default value in sqlite3?

Then you'd INSERT a value with meal_time = 'dinner' and select WHERE meal_time = 'dinner', for example.

At that point, your second question is trivial... 

SELECT fat from meals ORDER BY meal_time;

If you want the specific options of dinner, lunch, breakfast, then add a WHERE as shown.

Upvotes: 0

John Bravado
John Bravado

Reputation: 137

I would make 1 table and add a column called timeOfDay

in the database helper handler class

public ArrayList<Integer> getFats(int timeOfDay) {
    //0:breakfast
    //1:lunch
    //3:dinner
    //4:other
    ArrayList<Integer> result = new ArrayList<Integer>();
    SQLiteDatabase db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    String selectQuery = "SELECT * FROM TABLE WHERE timeOfDay = " + timeOfDay;
    Cursor cursor = db.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        result.add(cursor.getInt(cursor.getColumnIndex("fatColumn")));
        while (cursor.moveToNext()) {
            result.add(cursor.getInt(cursor.getColumnIndex("fatColumn")));
        }
    }
    cursor.close();
    db.close();
    return result;
}

Inside the activity you want to display information

ArrayList<Integer> fats = db.getFats(1);
int fatTotal = 0;
for(Integer fat : fats){
    //output string for this particular fat reading
    fatTotal += fat;
}
//output string of sum sort for totalFat

Upvotes: 2

Related Questions