CODEWITHSUNDEEP
sql
Geoff
Geoff

Reputation: 753

Getting COUNT from an exisiting resultset

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. :)

Upvotes: 1

Views: 79

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522331

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.

Upvotes: 1

Related Questions