Sum of rows in QSqlTableModel

[aekilic]

Dear All,

I have a table like this,

id(int) value(double) documentid(string)
-----------------------------------------------
sum(value)

What I would like to is, after I set filter to my model, I would like to insert a row with the sum(value) in the table. Is there any way that I could do this?

[d_stranz]

I think you might need to derive a custom QSqlTableModel and reimplement the data() method and rowCount() methods().

Your rowCount() method should return:

QSqlTableModel::rowCount() + 1  //  (for the sum row)

QSqlTableModel::rowCount() + 1  //  (for the sum row)

To copy to clipboard, switch view to plain text mode 

For all row and column values of the QModelIndex passed to the data() method except the ones corresponding to the summation row, simply return the value from QSqlTableModel::data(). Then in the last row, the column containing the "Total" string should return the corresponding QString in the variant, and the actual total column returns the sum as a long or floating point QVariant.

Something like this (not tested...):

QVariant MySqlTableModel::data( const QModelIndex & index, int role )
{
  QVariant v;
  if ( !index.isValid() )
    return v;
 
  long nDataRows = QSqlTableModel::rowCount();
  if ( index.row() < nDataRows )
     v = QSqlTableModel::data( index, role );
 
  if ( role == Qt::DisplayRole )
  {
    if ( index.row() == nDataRows )  // OK, because we add an extra row for the sum
    {
      if ( index.column() == 0 )
        v = QString( tr("Total") );
      else if ( index.column() == 1 )
        v = long( sumOfValues() );
    }
  }
  return v;
}

QVariant MySqlTableModel::data( const QModelIndex & index, int role )
{
  QVariant v;
  if ( !index.isValid() )
    return v;

  long nDataRows = QSqlTableModel::rowCount();
  if ( index.row() < nDataRows )
     v = QSqlTableModel::data( index, role );
  
  if ( role == Qt::DisplayRole )
  {
    if ( index.row() == nDataRows )  // OK, because we add an extra row for the sum
    {
      if ( index.column() == 0 )
        v = QString( tr("Total") );
      else if ( index.column() == 1 )
        v = long( sumOfValues() );
    }
  }
  return v;
}

To copy to clipboard, switch view to plain text mode 

You'll need to implement the "sumOfValues()" or issue another database query to return the sum.

[schnitzel]

or you cold do something like this (I'm assuming you filtered by documentid):

QSqlQuery q("SELECT SUM(value) FROM yourtable WHERE documentid='" + "whatever" + "'");
if(q.next())
{
    ui->le_mysum->setText("%1").arg(q.value(0).toDouble());
}

QSqlQuery q("SELECT SUM(value) FROM yourtable WHERE documentid='" + "whatever" + "'");
if(q.next())
{
    ui->le_mysum->setText("%1").arg(q.value(0).toDouble());
}

To copy to clipboard, switch view to plain text mode 

I didn't check the above code for syntax, but I hope you get idea.

I would add the sum in a line edit (or label) just below the table like this:
showtotal.jpg