How to access Sqlite3 aggregate recordset

Donald9307

I have a sqlite3 database with a column that holds QString data representing numbers. I’m able to execute the following aggregate SQL Select statement and obtain a recordset (single value).

SELECT SUM(amount)
FROM transactions
WHERE bkRef = 1;

bkRef is an Int datatype, and amount is a QString datatype.

I can successfully execute the above query in Qt6 but cannot figure out how to access the result of the query so I can pass the value to a lineEdit. Can anyone help me with this?

Christian Ehrlicher

@Donald9307 said in How to access Sqlite3 aggregate recordset:

I can successfully execute the above query in Qt6 but cannot figure out how to access the result of the query

See https://doc.qt.io/qt-6/qsqlquery.html#details

QSqlQuery query("SELECT country FROM artist");
while (query.next()) {
    QString country = query.value(0).toString();
    doSomething(country);
}

Donald9307

@Christian-Ehrlicher Thank you for your reply. What you provided isn't what I need. I know how to access a particular column in my database from the query example you gave. Rather, I need to access the aggregate function (SUM) results from my SQL query. My query creates a virtual recordset "sum(amount)," containing one data element, namely, the sum of the amount column where the bkRef = 1. I would be most grateful if you could help me to figure that out.

Christian Ehrlicher

It's query.value(0) - the first item in the first record set.

Donald9307

@Christian-Ehrlicher when my query executes and I invoke query.value(0) I get the following error message:

QSqlQuery::value: not positioned on a valid record

Apparently the recordset returned from the SUM aggregate function is not associated with query.value(int). Do you have any other suggestions to try?

SGaist

@Donald9307 hi,

Did you call next once before getting the value ?

Donald9307

@SGaist Hi. No I didn't. That fixed the problem I was having. Thanks so much for your help.