Af37C0bY

1How to sum a column of a Db
2String rawQuery = 
3    "SELECT _id, fehlzeiten, 
4         SUM(fehlzeiten) as sum 
5         FROM notes";
6     sqliteCallLogsDB.rawQuery(rawQuery, null);
7	
8public class NotesDbAdapter {
9
10public static final String KEY_TITLE = "title";
11public static final String KEY_BODY = "body";
12public static final String KEY_ROWID = "_id";
13public static final String KEY_Test = "test";
14public static final String KEY_Time = "fehlzeiten";
15private static final String TAG = "NotesDbAdapter";
16private DatabaseHelper mDbHelper;
17private SQLiteDatabase mDb;
18
19/**
20 * Database creation sql statement
21 */
22
23private static final String DATABASE_CREATE =
24    "create table notes (_id integer primary key autoincrement, "
25    + "title text not null, body text not null, fehlzeiten text not null, test text not null);";
26
27private static final String DATABASE_NAME = "data";
28private static final String DATABASE_TABLE = "notes";
29private static final int DATABASE_VERSION = 4;
30
31private final Context mCtx;
32
33private static class DatabaseHelper extends SQLiteOpenHelper {
34
35    DatabaseHelper(Context context) {
36        super(context, DATABASE_NAME, null, DATABASE_VERSION);
37    }
38
39    @Override
40
41    public void onCreate(SQLiteDatabase db) {
42
43        db.execSQL(DATABASE_CREATE);
44
45    }
46
47    @Override
48    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
49
50        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
51                + newVersion + ", which will destroy all old data");
52        db.execSQL("DROP TABLE IF EXISTS notes");
53        onCreate(db);
54    }
55}
56
57/**
58 * Constructor - takes the context to allow the database to be
59 * opened/created
60 * 
61 * @param ctx the Context within which to work
62 */
63public NotesDbAdapter(Context ctx) {
64
65    this.mCtx = ctx;
66}
67
68/**
69 * Open the notes database. If it cannot be opened, try to create a new
70 * instance of the database. If it cannot be created, throw an exception to
71 * signal the failure
72 * 
73 * @return this (self reference, allowing this to be chained in an
74 *         initialization call)
75 * @throws SQLException if the database could be neither opened or created
76 */
77public NotesDbAdapter open() throws SQLException {
78
79    mDbHelper = new DatabaseHelper(mCtx);
80    mDb = mDbHelper.getWritableDatabase();
81    return this;
82}
83
84public void close() {
85    mDbHelper.close();
86}
87
88
89/**
90 * Create a new note using the title and body provided. If the note is
91 * successfully created return the new rowId for that note, otherwise return
92 * a -1 to indicate failure.
93 * 
94 * @param title the title of the note
95 * @param body the body of the note
96 * @return rowId or -1 if failed
97 */
98public long createNote(String title, String body, String fehlzeit, String test) {
99
100    ContentValues initialValues = new ContentValues();
101    initialValues.put(KEY_TITLE, title);
102    initialValues.put(KEY_BODY, body);
103    initialValues.put(KEY_Time, fehlzeit);
104    initialValues.put(KEY_Test, test);
105
106    return mDb.insert(DATABASE_TABLE, null, initialValues);
107}
108
109/**
110 * Delete the note with the given rowId
111 * 
112 * @param rowId id of note to delete
113 * @return true if deleted, false otherwise
114 */
115public boolean deleteNote(long rowId) {
116
117    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
118}
119
120/**
121 * Return a Cursor over the list of all notes in the database
122 * 
123 * @return Cursor over all notes
124 */
125public Cursor fetchAllNotes() {
126
127    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
128            KEY_BODY,KEY_Time,KEY_Test}, null, null, null, null, null);
129}
130
131
132
133
134/**
135 * Return a Cursor positioned at the note that matches the given rowId
136 * 
137 * @param rowId id of note to retrieve
138 * @return Cursor positioned to matching note, if found
139 * @throws SQLException if note could not be found/retrieved
140 */
141public Cursor fetchNote(long rowId) throws SQLException {
142
143    Cursor mCursor =
144
145        mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
146                KEY_TITLE, KEY_BODY,KEY_Time,KEY_Test}, KEY_ROWID + "=" + rowId, null,
147                null, null, null, null);
148    if (mCursor != null) {
149        mCursor.moveToFirst();
150    }
151    return mCursor;
152}
153
154
155/**
156 * Update the note using the details provided. The note to be updated is
157 * specified using the rowId, and it is altered to use the title and body
158 * values passed in
159 * 
160 * @param rowId id of note to update
161 * @param title value to set note title to
162 * @param body value to set note body to
163 * @return true if the note was successfully updated, false otherwise
164 */
165public boolean updateNote(long rowId, String title, String body, String fehlzeiten, String test) {
166
167    ContentValues args = new ContentValues();
168    args.put(KEY_TITLE, title);
169    args.put(KEY_BODY, body);
170    args.put(KEY_Time, fehlzeiten);
171    args.put(KEY_Test, test);
172
173    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
174}
175	
176public long getFehlzeitenSum() {
177    long result = 0;
178    Cursor c = mDb.query(DATABASE_TABLE, 
179      new String[] { "sum(" + KEY_Time + ")" },
180      null, 
181      null, 
182      null /* you may add a GROUP BY attribute (e.g. KEY_Test) here */,
183      null /* and here a HAVING for your group-by-clause */, 
184      null);
185    if (c.moveToFirst()) {
186        result = c.getLong(0);
187    }
188    c.close();
189    return result;
190}
191	
192SELECT SUM(fehlzeiten) as sum FROM notes