sqlite find total row count for database

Question

I've spent loads of time searching around trying to figure this out but I can find a solution.

I'm trying to return the total row count in my database

Here is what I do to pull all the genre of a specific type.

var film_list = tx.executeSql('SELECT * FROM mydb WHERE genre = "Comedy"');

how can I do something like

 var film_total = tx.executeSql('SELECT * FROM mydb TOTAL_ROW_COUNT "');
 console.log(film_total)

http://stackoverflow.com/questions/3615636/select-count-in-sqlite — Robert Harvey, Jul 09 '13 at 22:40

score 2 · Accepted Answer · answered Jul 09 '13 at 22:39

2

You'll have to iterate through each table, and add up the individual counts.

SELECT Count(*) FROM TableName

answered Jul 09 '13 at 22:39

Curt

5,518
1
21
35

Thanks Curt so something like (can't figure it out) 'film_item = film_total.rows.item(count).image' – Wanting to learn Jul 09 '13 at 22:58
1

I can't really understand the question. First get a list of the tables in your DB (unless you already know them all). You can do this by querying the sqlite_master table, (where type = 'table'). Then, for each one, run the query I wrote above, substituting each tablename from the previous query for "TableName" in the example. Each query will return a single row of a single column: the integer count of rows in that table. Add them all up, and you've got a count of all the rows in your database. – Curt Jul 09 '13 at 23:05
I'm not sure how to iterate through the tables to get the total count – Wanting to learn Jul 09 '13 at 23:06
1

On re-reading your question, and Derek's answer, it now seems to me that you really just need the count of rows in the *table* MyDb (not in the database as a whole). If that's the case, just execute the SQL command "SELECT Count( * ) FROM myDB", and read the result. If you want them broken down by genre, execute the query: "SELECT genre, Count(*) AS cnt FROM MyDB GROUP BY genre" – Curt Jul 09 '13 at 23:27

score 1 · Answer 2 · answered Jul 09 '13 at 23:17

var totalRowCount = 0;   
var genres = new Array("Comedy","Horror","Romantic","Action","Thriller","Indy");
for(var i = 0; i < genres.length; i++){
    var cmdString ="SELECT COUNT(*) FROM mydb WHERE genre = '";
    cmdString.concat(genres[i];
    var genreCount = tx.executeSql(cmdString);
    totalRowCount +=  genreCount;
}

sqlite find total row count for database

2 Answers2