FMDB SQLite question: row count of a query?

Question

does anyone know how to return the count of a query when using FMDB? If I executeQuery @"select count(*) from sometable were..." I get an empty FMResultSet back. How can I get the row count of the query? Do I need to do a query like "select * from sometable where.." and iterate through the result set? Or can I use useCount or whats the best way (in terms of performance) to do this?

Thanks!

Ben Baron · Accepted Answer · 2013-12-10T18:26:51.917

89

Shorter code to accomplish the same thing:

NSUInteger count = [db intForQuery:@"SELECT COUNT(field) FROM table_name"];

Make sure to include the FMDatabaseAdditions.h header file to use intForQuery:.

edited Dec 10 '13 at 18:26

answered Dec 01 '10 at 00:18

Ben Baron

13,799
12
50
65

Is there a something like "intForQueryWithFormat"? – almas Feb 12 '15 at 04:34
1

The regular implementation of intForQuery already takes a variable argument list. Just put question marks where you want variables used, and then put them at the end of the call like this: `NSUInteger count = [db intForQuery:@"SELECT COUNT(?) FROM ?", fieldName, tableName];` – Ben Baron Feb 12 '15 at 22:40

score 21 · Answer 2 · answered Nov 22 '10 at 18:16

try this. It works for me. Iterating all the records is not recommended.

FMResultSet *rs = [db executeQuery:@"select count(FIELD) as cnt from TABLENAME"];
while ([rs next]) {
    NSLog(@"Total Records :%d", [rs intForColumn:@"cnt"]);
}

May be you should check your Where clause.

score 5 · Answer 3 · answered Jan 20 '16 at 16:17

Swift 2 Example

This code snippet will print the count for you.

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsInArray: nil) {
    while rs.next() {
        print("Total Records:", rs.intForColumn("Count"))
    }
}

If it did not work, a few suggestions:

a) Look for a line in your project that says let database = or var database =. If you find one then change db to database
b) Did you change the TABLE_NAME in the Select statement to whatever your table is called?

score 3 · Answer 4 · answered Dec 20 '13 at 04:45

The first one is also right but by using this method you can retrieve records and count using the same query , no headache to write another one. Just add count(*) as count to your query.

You could always just run the proper SQL statement. I do something like:

FMResultSet *rs = [database executeQuery:@"select count(*) as count from words"];
[rs next];

wordsThatExist = [rs intForColumn:@"count"];

Setting up the SQL query may be quicker and cheaper then iterating.. I believe counts are cheap.

score 1 · Answer 5 · answered Feb 07 '17 at 14:09

1

updated for Swift 3 minor change to "int For Column"

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsInArray: nil) {
while rs.next() {
    print("Total Records:", rs.int(forColumn: "Count"))
    }
}

answered Feb 07 '17 at 14:09

Chris L

61
1
7

score 1 · Answer 6 · answered Dec 20 '18 at 20:34

1

updated for Swift 4 minor change in method parameter name

if let rs = db.executeQuery("SELECT COUNT(*) as Count FROM TABLE_NAME", withArgumentsIn: nil) {
while rs.next() {
    print("Total Records:", rs.int(forColumn: "Count"))
    }
}

answered Dec 20 '18 at 20:34

HalR

10,678
5
43
74

score 0 · Answer 7 · edited Jul 07 '16 at 13:35

Please Try Following Code, this works for me

let objManager = ModelManager.getInstance()

objManager.database?.open()

let resultSet1: FMResultSet! = sharedInstance.database!.executeQuery("SELECT COUNT(Field) FROM TableName”, withArgumentsInArray:nil)

        if (resultSet1 != nil)
        {
            while resultSet1.next()
            {
              countRecord = Int(resultSet1.intForColumn("COUNT(Field)"))
            }

        }

     print(countRecord)

You Will get Count of Field

score -5 · Answer 8 · answered Jan 08 '15 at 09:58

-5

If you want to know the count of the rows before make something with the result, you can even do a simple query and ask for the results columnCount that give you the number of rows and you can save one query if you really want to make something with the resultSet

FMResultSet *results = [database executeQuery:@"SELECT * from tableName"];
int numberOfRows = [results columnCount];

while ([results next]){
... do your stuff ...
}

answered Jan 08 '15 at 09:58

Bahamonde

1
1

2

Doesn't columnCount give you the count of columns and not rows? I don't believe this is a correct answer. – Matt C. Mar 19 '15 at 15:27

FMDB SQLite question: row count of a query?

8 Answers8

Swift 2 Example

Linked