CODEWITHSUNDEEP
iphonesqlite
Mario
Mario

Reputation: 173

how to count rows from multiple tables in sqlite

i am using following code

  - (int)GetTextCount
  {

    NSMutableArray *audioArray=[[NSMutableArray alloc]init];



    int count = 0;
//This method is defined to retrieve data from Database


NSString *dbPath=filePath;

sqlite3 *database;

if(sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

    // Setup the SQL Statement and compile it for faster access
    /*
    SELECT  (
             SELECT COUNT(*)
             FROM   tab1
             ) AS count1,
    (
     SELECT COUNT(*)
     FROM   tab2
     ) AS count2
    FROM    dual
    */


    const char *sqlStatement = "select count(*) from photo where mid=? ";
    //const char *sqlStatement = "select * from textt where mid=?";
    sqlite3_stmt *compiledStatement;


    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

        sqlite3_bind_int(compiledStatement, 1, memoryData.memoryId);

        //(compiledStatement, 1, [header UTF8String], -1, SQLITE_TRANSIENT);

        while(sqlite3_step(compiledStatement) == SQLITE_ROW) {              
            AudioData *data=[[AudioData alloc]init];
            //create the MemoryData object to store the data of one record


            // Read the data from the result row

            int pId=sqlite3_column_int(compiledStatement, 0);
            NSLog(@"total audiosssss are %i",pId);


            //NSString *filePath=[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];


            //filePath=[self retrievePath:filePath];

            //[data setAudioId:pId];
            //[data setFilePath:filePath];
            //Store every object of MemoryData in t
            [audioArray addObject:data];


        } // end of the while


    }
    sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);
return [audioArray count];
  }

To count from one table , but I need to count the rows from four tables , so now what i am doing that running separate queries which reduces the performance so I want to run one query to select from four tables , please help how can I do that?

Upvotes: 3

Views: 6028

Answers (3)

EmptyStack
EmptyStack

Reputation: 51374

I think you want this query. (Sorry If I am wrong).

SELECT (SELECT count(*) from table_1  where mid = ?) + 
       (SELECT count(*) from table_2  where mid = ?) +
       (SELECT count(*) from table_3  where mid = ?) +
       (SELECT count(*) from table_4  where mid = ?)

Upvotes: 12

p.campbell
p.campbell

Reputation: 100587

Try this:

SELECT COUNT(*) AS MyCount, 'MyTable1Count' AS Description FROM Table1
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable2Count' AS Description FROM Table2
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable3Count' AS Description FROM Table3
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable4Count' AS Description FROM Table4

This will produce a resultset like:

MyCount   Description
----------------------
534      MyTable1Count
33       MyTable2Count
92843    MyTable3Count
931      MyTable4Count

Upvotes: 5

Mobile Developer iOS Android
Mobile Developer iOS Android

Reputation: 6016

There are several ways to do this task,

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

or you can use,

SELECT COUNT(DISTINCT member.id), COUNT(DISTINCT thread.id), COUNT(DISTINCT post.id) FROM member, thread, post;

Upvotes: 1

Related Questions