Getting COUNT from an exisiting resultset

Question

I have are resultset based on this SELECT statement:

"SELECT ca.mem_no, me.mem_surname, me.mem_first_name, " +
    "lcc.call_cat, ca.call_location, ca.caller_name, lsc.call_sub_cat, ca.call_id, " +
    "call_date_start, ca.call_note_start, ca.call_date_end, ca.call_duration, lcs.call_status, " +
    "lca.call_action, lcr.call_res, ca.call_note_res\n" +
    "FROM tblCall ca\n" +
    "INNER JOIN tlkpCallStatus lcs on lcs.callstatus_id = ca.callstatus_id\n" +
    "INNER JOIN tlkpCallAction lca on lca.callaction_id = ca.callaction_id\n" +
    "INNER JOIN tlkpCallResolution lcr on lcr.callres_id = ca.callres_id\n" +
    "LEFT OUTER JOIN tlkpCallSubCategory lsc on lsc.callsubcat_id = ca.callsubcat_id\n" +
    "INNER JOIN tlkpCallCategory lcc on lcc.call_cat_id = ca.call_cat_id\n" +
    "LEFT OUTER JOIN tblMember me on me.mem_no = ca.mem_no\n" +
    "INNER JOIN tblClient cl on cl.client_ident = me.client_ident\n" +
    "WHERE me.client_ident = \'AVA\'" +
    "AND ca.call_date_start BETWEEN '2017-02-01' AND '2017-02-28'\n" +
    "ORDER BY  date(ca.call_date_start);\n"
    ;

which will rst.next() to a speadsheet ... i realize i can get the count of rows by waiting for the processing to finish, however i need the row count prior to writing the report .. i am faced with writing another pre sql statement getting COUNT(*) based upon the same JOIN and WHERE conditions. But i don't want to have two copies of basically the one sql statement ..

Is there a way (JAVA, Sqlite3) i can "SELECT COUNT(*)" from the existing resultset? ... seems a waste to have to go and get them all again just to be able to count the rows. :)

Why do you need to know the count before writing the report? — Tim Biegeleisen, Nov 01 '18 at 01:44
@dwir182 -- the resultset is a java object that i am curious as to whether or not it can be re-quieried .. like if it were a 'virtual table' let's say. COUNT is one thing i need but there would be others. But as explained by Tim .. easier to go back to the DB!! Thanks. — Geoff, Nov 01 '18 at 05:59

score 1 · Accepted Answer · answered Nov 01 '18 at 01:47

There is a way to probe a JDBC ResultSet and find out how many records are in that result set. But, it might cause the entire result set to be read across the network. Therefore, I vote for just running a separate COUNT(*) query if you really need to find the count. From a network usage point of view, this is very cheap, because you are just asking for a single number to be sent across.

There is also a SQL solution here, which unfortunately is not available for SQLite, which does not support analytic functions (yet). You could use the following query:

SELECT
    ca.mem_no,
    me.mem_surname,
    me.mem_first_name,
    COUNT(*) OVER () AS total_record_count    -- change here
    ...
FROM

That is, we can use COUNT as an analytic function to find the total record count at the same time as running the rest of your original query. Again, not available for SQLite, but might be an option for other databases.

Getting COUNT from an exisiting resultset

1 Answers1