SQLite子查询中where子句中列的总和

问题描述：

我在查询数据库以获取最近24小时内输入的值的游标。我可以查询并获取所需的A_VALUE列的所有值，但我无法弄清楚如何以及在哪里正确编写子查询，以便使用where子句参数获取A_VALUE列的总和并获取其值。任何想法将不胜感激。（具有零编码的背景和我正在学习从这个网站的一切，其他人）SQLite子查询中where子句中列的总和

//当前代码来获取过去24小时内的值是：

String[] projection = { 
      DrinkContract.DrinkEntry._ID, 
      DrinkContract.DrinkEntry.COLUMN_A_VALUE, 
      DrinkContract.DrinkEntry.COLUMN_TIME_DATE}; 

    //code to query databse for last 24 hours 

    String selection = DrinkContract.DrinkEntry.COLUMN_TIME_DATE + " BETWEEN strftime('%s', 'now', '-1 day') AND strftime('%s','now') "; 

    Cursor dayCursor = getContentResolver().query(
      DrinkContract.DrinkEntry.CONTENT_URI, // Provider content URI to query 
      projection,    // Columns to include in the resulting Cursor 
      selection,     // selection clause 
      null, 
      DrinkContract.DrinkEntry.COLUMN_TIME_DATE); // Default sort order*/ 

    if (dayCursor == null || dayCursor.getCount() < 1) { 
     return; 
    } 

    // Proceed with moving to the first row of the cursor and reading data from it 

    while (dayCursor.moveToNext()) { 

     int aValueColumnIndex = dayCursor.getColumnIndex(DrinkContract.DrinkEntry.COLUMN_A_VALUE); 
     int drinkTimeDateColumnIndex = dayCursor.getColumnIndex(DrinkContract.DrinkEntry.COLUMN_TIME_DATE); 

     // Extract out the value from the Cursor for the given column index 
     double aValue = dayCursor.getDouble(aValueColumnIndex); 


     System.out.println("The total a value is " + aValue);

总和是单个值，并且是从多行计算得出的。查询中其他两列的值应该是多少？ –

我不需要其他栏目的总和。从我读过的内容来看，它应该像在SELECT或WHERE子句中添加sum（DrinkContract.DrinkEntry.COLUMN_A_VALUE）或total（DrinkContract.DrinkEntry.COLUMN_A_VALUE）一样简单，以获得该特定列的总和或总数，但我可以似乎没有得到任何版本的工作。感谢您的回复 – BSGpegasus

请提供一些样本数据和您想要的输出。（请参阅[如何格式化堆栈溢出中的SQL表后？]（https://meta.stackexchange.com/q/96125）如何添加一些。） –

答

//工作代码

String[] projection = { 
     DrinkContract.DrinkEntry._ID, 
     DrinkContract.DrinkEntry.COLUMN_TIME_DATE, 
      "total("+DrinkContract.DrinkEntry.COLUMN_A_VALUE+") AS totalA "}; 

//code to query databse for last 24 hours 

String selection = DrinkContract.DrinkEntry.COLUMN_TIME_DATE + " BETWEEN strftime('%s', 'now', '-1 day') AND strftime('%s','now') "; 

Cursor dayCursor = getContentResolver().query(
     DrinkContract.DrinkEntry.CONTENT_URI, // Provider content URI to query 
     projection,    // Columns to include in the resulting Cursor 
     selection,     // selection clause 
     null, 
     DrinkContract.DrinkEntry.COLUMN_TIME_DATE); // Default sort order*/ 

if (dayCursor == null || dayCursor.getCount() < 1) { 
    return; 
} 

// Proceed with moving to the first row of the cursor and reading data from it 

while (dayCursor.moveToNext()) { 

    int aValueColumnIndex = dayCursor.getColumnIndex("totalA"); 
    int drinkTimeDateColumnIndex = dayCursor.getColumnIndex(DrinkContract.DrinkEntry.COLUMN_TIME_DATE); 

    // Extract out the value from the Cursor for the given column index 
    double aValue = dayCursor.getDouble(aValueColumnIndex); 


    System.out.println("The total a value is " + aValue);

SQLite子查询中where子句中列的总和

相关推荐