Getting Number of Rows in a Group By Query with Microsoft Access

Question

I have basically the same problem outlined in this question, however I am using Microsoft Access as a database instead of MySQL. The result of which is that SQL_CALC_FOUND_ROWS doesn't seem to be available to me. Believe me, I want to switch, but for the moment it is out of the question.

I have a query that aggregates a number of rows, essentially looking for repeat rows based on certain keys, using a group by. It looks something like this:

Select key1, key2, key3, Count(id) 
from table 
group by key1, key2, key3 
having Count(id) > 1

I need to determine the number of rows (or groupings) that query will return.

The database is being accessed through Java, so in theory I could simply run the query, and cycle through it twice, but I was hoping for something faster and preferably SQL based. Any ideas?

Answer 1

MS Access's record count should give you what you need, or am I missing something?

If you need distinct values from keys, try this

SELECT COUNT(*) AS Expr2
  FROM (
        SELECT DISTINCT [key1] & "-" & [key2] & "-" & [key3] AS Expr1
          FROM Table1
       ) AS SUB;

Answer 2

When you create the Statement object, you can declare it to be scrollable. Then the first thing you do is scroll to the end and get the record number. As you're looking at the last record, this will be the number of records in the result set. Then scroll back to the beginning and process normally. Something like:

Statement st=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs=st.executeQuery(myQueryString);
boolean any=rs.last();
int count = any ? count=getRow()  : 0;
... do whatever with the record count ...
rs.first();
while (rs.next())
{
... whatever processing you want to do ...
}
rs.close();
... etc ...

I have no idea what the performance implications of doing this with MS Access will be, whether it can jump directly to the end of the result set or if it will have to sequentially read all the records. Still, it should be faster than executing the query twice.