SQLite的：在一列中获取所有值的总和 [英] SQLite: Obtaining total of all values in one column

问题描述

我想创建一个查询，这将使所有值的总和在我的SQLite datase中的一列。

我想这个查询是返回总作为一个int，这样我可以再进一步进行不同的活动中处理它的方法中。

我怎么可以这样做？

我已经创建了数据库辅助类中类似的方法（见下文），但我不知道如何实现内SQLite的查询给予总。

宣言和数据库的建立在Databasehelper：（我想总的列 COL_MED ）

 公共类DatabaseHelper扩展SQLiteOpenHelper {    //数据库版本
    私有静态最终诠释DATABASE_VERSION = 10;    //数据库名称
    私人最终静态字符串DATABASE_NAME =MeditationDatabase;    //联系人表名
    私有静态最后弦乐TABLE_SCORE =算账;    //联系方式表列名
    私有静态最后弦乐COL_SESSION =的SessionID;
    私有静态最后弦乐COL_GAMETITLE =游戏;
    私有静态最后弦乐COL_NAME =名;
    私有静态最后弦乐COL_MED =avgmeditation;
    私有静态最后弦乐COL_MAX =maxmeditation;
    私有静态最后弦乐COL_AVGATT =avgattention;
    私有静态最后弦乐COL_MAXATT =maxattention;
    私有静态最后弦乐COL_SCORE =分数;
    私有静态最后弦乐COL_DATE =日期;    / **
     *构造
     *
     * @参数方面
     * /
    公共DatabaseHelper（上下文的背景下）{
        超（背景下，DATABASE_NAME，空，DATABASE_VERSION）;
    }    / **
     *用于创建数据库的方法
     * /
    @覆盖
    公共无效的onCreate（SQLiteDatabase DB）{        字符串CREATE_TABLE_SCORE =CREATE TABLE+ TABLE_SCORE +（+ COL_SESSION
                +STRING PRIMARY KEY，+ COL_GAMETITLE +字符串+ COL_NAME +字符串+ COL_MED +INTEGER
                 + COL_MAX +INTEGER，+ COL_AVGATT +INTEGER，+ COL_MAXATT +INTEGER，+ COL_SCORE +INTEGER，+ COL_DATE +STRING+）;
        db.execSQL（CREATE_TABLE_SCORE）;    }
 

，返回在DB中的条目数潜在相似的方法：

 公众诠释getTotalGamesPlayed（）{
    SQLiteDatabase分贝= this.getWritableDatabase（）;
    尝试{
        回报（INT）DatabaseUtils.queryNumEntries（DB，TABLE_SCORE）;
    } {最后
        db.close（）;
    }
}
 

编辑：

这是正确的查询：

 查询字符串=SELECT SUM（COL_MED）FROM+ TABLE_SCORE;
 

解决方案

阅读文档;您可以使用和（）或共有（）。

列的名称不是 COL_MED ;即，其值是该列的名称的符号的名称。
像 TABLE_SCORE ，你将不得不插入它的值写入SQL查询：

INT getSumOfAllAvgMeditations（）{
    SQLiteDatabase分贝= getWritableDatabase（）;
    尝试{
        SQL字符串=选择Total（+ COL_MED +）FROM+ TABLE_SCORE;
        回报（INT）DatabaseUtils.longForQuery（DB，SQL，NULL）;
    } {最后
        db.close（）;
    }
}


I want to create a Query that will give the total of all values in a single column of my SQLite datase.

I want this query to be within a method that returns the total as an int so that I can then further process it within a different activity.

How can I do so?

I have already created similar methods within my Database helper class (see below), but I do not know how to implement a query within SQLite to give the total.

Declaration and creation of database in Databasehelper: (the column i want the total for is COL_MED)

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 10;

    // Database Name
    private final static String DATABASE_NAME = "MeditationDatabase";

    // Contacts table name
    private static final String TABLE_SCORE = "scores";

    // Contacts Table Columns names
    private static final String COL_SESSION = "sessionid";
    private static final String COL_GAMETITLE = "game";
    private static final String COL_NAME = "name";
    private static final String COL_MED = "avgmeditation";
    private static final String COL_MAX = "maxmeditation";
    private static final String COL_AVGATT = "avgattention";
    private static final String COL_MAXATT = "maxattention";
    private static final String COL_SCORE = "score";
    private static final String COL_DATE = "date";

    /**
     * Constructor
     * 
     * @param context
     */
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * Method that creates the database
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_TABLE_SCORE = "CREATE TABLE " + TABLE_SCORE + "(" + COL_SESSION
                + " STRING PRIMARY KEY, " + COL_GAMETITLE + " STRING, "  + COL_NAME + " STRING, " + COL_MED + " INTEGER, "
                 + COL_MAX + " INTEGER, " + COL_AVGATT + " INTEGER, " + COL_MAXATT + " INTEGER, "  + COL_SCORE +  " INTEGER, " + COL_DATE + " STRING " + ")";
        db.execSQL(CREATE_TABLE_SCORE);

    }

Potentially similar method that returns the number of entries in the DB:

public int getTotalGamesPlayed() {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
        return (int)DatabaseUtils.queryNumEntries(db, TABLE_SCORE);
    } finally {
        db.close();
    }
}

EDIT:

Is this the correct query:

String query = "SELECT SUM(COL_MED) FROM " + TABLE_SCORE;

解决方案

Read the documentation; you can use either sum() or total().

The name of the column is not COL_MED; that is the name of the symbol whose value is the name of the column. Like TABLE_SCORE, you would have to insert its value into the SQL query:

int getSumOfAllAvgMeditations() {
    SQLiteDatabase db = getWritableDatabase();
    try {
        String sql = "SELECT TOTAL(" + COL_MED + ") FROM " + TABLE_SCORE;
        return (int)DatabaseUtils.longForQuery(db, sql, null);
    } finally {
        db.close();
    }
}

这篇关于SQLite的：在一列中获取所有值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！