#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QAbstractItemModel>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QStandardItemModel>
#include <QTreeView>
#include <QHeaderView>
/* Given a data model, a column to group by, a column to aggregate and an
aggregate function, this function will return a model with the given
transformation. Available aggregate function include SUM, AVG, COUNT, MAX
and MIN.
*/
unsigned int groupByColumn,
unsigned int aggregateColumn,
const QString & aggregateFunction
= "SUM") {
/* Open an in-memory database. */
db.setDatabaseName(":memory:");
bool ok = db.open();
/* Create a table with the correct number of columns. */
for (int i = 0; i < m.columnCount(); i++)
columns.
push_back(QString("F%1").
arg(i
));
ok = query.exec(sql);
/* Set up a table model of our newly created table. */
tbl.setTable("T1");
tbl.insertRows(0, m.rowCount());
/* Now copy accross our model to the newly created table. */
for (int i = 0; i < m.rowCount(); i++)
for (int j = 0; j < m.columnCount(); j++)
tbl.setData(tbl.index(i, j), m.data(m.index(i, j)));
/* Submit changes to database. */
ok = tbl.submitAll();
/* Finally, query our database for the grouped by table. */
arg(groupByColumn).
arg(aggregateColumn).
arg(aggregateFunction);
out->setQuery(sql2, db);
return out;
}
int main(int argc, char * argv[])
{
for (int row = 0; row < 4; ++row)
{
model.setItem(row, 0, item);
model.setItem(row, 1, item);
model.setItem(row, 2, item);
}
for (int row = 4; row < 8; ++row)
{
model.setItem(row, 0, item);
model.setItem(row, 1, item);
model.setItem(row, 2, item);
}
tbl->setHeaderData(0, Qt::Horizontal, "Total");
tv.setModel(tbl);
tv.header()->moveSection(0, 3);
tv.show();
return a.exec();
}
#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QAbstractItemModel>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QStandardItemModel>
#include <QTreeView>
#include <QHeaderView>
/* Given a data model, a column to group by, a column to aggregate and an
aggregate function, this function will return a model with the given
transformation. Available aggregate function include SUM, AVG, COUNT, MAX
and MIN.
*/
QAbstractItemModel* doGroupby(const QAbstractItemModel & m,
unsigned int groupByColumn,
unsigned int aggregateColumn,
const QString & aggregateFunction = "SUM")
{
/* Open an in-memory database. */
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
bool ok = db.open();
/* Create a table with the correct number of columns. */
QStringList columns;
for (int i = 0; i < m.columnCount(); i++)
columns.push_back(QString("F%1").arg(i));
QString sql = QString("CREATE TABLE T1(%1)").arg(columns.join(","));
QSqlQuery query(db);
ok = query.exec(sql);
/* Set up a table model of our newly created table. */
QSqlTableModel tbl(0, db);
tbl.setTable("T1");
tbl.setEditStrategy(QSqlTableModel::OnManualSubmit);
tbl.insertRows(0, m.rowCount());
/* Now copy accross our model to the newly created table. */
for (int i = 0; i < m.rowCount(); i++)
for (int j = 0; j < m.columnCount(); j++)
tbl.setData(tbl.index(i, j), m.data(m.index(i, j)));
/* Submit changes to database. */
ok = tbl.submitAll();
/* Finally, query our database for the grouped by table. */
QSqlQueryModel * out = new QSqlQueryModel;
QString sql2 = QString("SELECT %3(F%2), * FROM T1 GROUP BY F%1").
arg(groupByColumn).
arg(aggregateColumn).
arg(aggregateFunction);
out->setQuery(sql2, db);
return out;
}
int main(int argc, char * argv[])
{
QApplication a(argc, argv);
QStandardItemModel model(8, 3);
for (int row = 0; row < 4; ++row)
{
QStandardItem *item = new QStandardItem(QString("test"));
model.setItem(row, 0, item);
item = new QStandardItem(QString("%1").arg(1));
model.setItem(row, 1, item);
item = new QStandardItem(QString("row %0").arg(row));
model.setItem(row, 2, item);
}
for (int row = 4; row < 8; ++row)
{
QStandardItem *item = new QStandardItem(QString("test2"));
model.setItem(row, 0, item);
item = new QStandardItem(QString("%1").arg(5));
model.setItem(row, 1, item);
item = new QStandardItem(QString("row %0").arg(row));
model.setItem(row, 2, item);
}
QTreeView tv;
QAbstractItemModel * tbl = doGroupby(model, 0, 1, "SUM");
tbl->setHeaderData(0, Qt::Horizontal, "Total");
tv.setModel(tbl);
tv.header()->moveSection(0, 3);
tv.show();
return a.exec();
}
To copy to clipboard, switch view to plain text mode
Bookmarks