CODEWITHSUNDEEP
sqlitecount
user26404
user26404

Reputation: 1391

How to get the number of rows of the selected result from sqlite3?

I want to get the number of selected rows as well as the selected data. At the present I have to use two sql statements:

one is 

select * from XXX where XXX;

the other is 

select count(*) from XXX where XXX;

Can it be realised with a single sql string?

I've checked the source code of sqlite3, and I found the function of sqlite3_changes(). But the function is only useful when the database is changed (after insert, delete or update).

Can anyone help me with this problem? Thank you very much!

Upvotes: 40

Views: 101030

Answers (9)

Antonio_60_Italy
Antonio_60_Italy

Reputation: 1

If you want, you can know the number of records generated by the query simply like this

command = "SELECT * FROM table_name WHERE (name1)=? and (name2)=? ....."
cursor.execute(command, (aaaaa,bbbb,........))
rows = cursor.fetchall()
record_number = len (rows)

If the number is greater than zero it will be possible to access the data of the individual fields using a query of the type

k = rows[a][b]

where the value of 'a' represents the number of the record to be accessed and the value of 'b' represents the position of the field you want to acquire

Upvotes: 0

nobur
nobur

Reputation: 11

For those who are still looking for another method, the more elegant one I found to get the total of row was to use a CTE. this ensure that the count is only calculated once :

WITH cnt(total) as (SELECT COUNT(*) from xxx) select * from xxx,cnt

the only drawback is if a WHERE clause is needed, it should be applied in both main query and CTE query.

In the first comment, Alttag said that there is no issue to run 2 queries. I don't agree with that unless both are part of a unique transaction. If not, the source table can be altered between the 2 queries by any INSERT or DELETE from another thread/process. In such case, the count value might be wrong.

Upvotes: 1

user9608133
user9608133

Reputation:

To get the number of unique titles, you need to pass the DISTINCT clause to the COUNT function as the following statement:

SELECT
 COUNT(DISTINCT column_name)
FROM
 'table_name';

Source: http://www.sqlitetutorial.net/sqlite-count-function/

Upvotes: 1

Artem Aleksandrov
Artem Aleksandrov

Reputation: 352

try this way

select (select count() from XXX) as count, * 
from XXX;

Upvotes: 16

FormatD
FormatD

Reputation: 21

select (select COUNT(0) 
            from xxx t1 
            where t1.b <= t2.b 
            ) as 'Row Number', b from xxx t2 ORDER BY b;

just try this.

Upvotes: 2

Aaron Digulla
Aaron Digulla

Reputation: 328556

SQL can't mix single-row (counting) and multi-row results (selecting data from your tables). This is a common problem with returning huge amounts of data. Here are some tips how to handle this:

  • Read the first N rows and tell the user "more than N rows available". Not very precise but often good enough. If you keep the cursor open, you can fetch more data when the user hits the bottom of the view (Google Reader does this)

  • Instead of selecting the data directly, first copy it into a temporary table. The INSERT statement will return the number of rows copied. Later, you can use the data in the temporary table to display the data. You can add a "row number" to this temporary table to make paging more simple.

  • Fetch the data in a background thread. This allows the user to use your application while the data grid or table fills with more data.

Upvotes: 16

Jes&#250;s A. &#193;lvarez
Jes&#250;s A. &#193;lvarez

Reputation: 2966

If you use sqlite3_get_table instead of prepare/step/finalize you will get all the results at once in an array ("result table"), including the numbers and names of columns, and the number of rows. Then you should free the result with sqlite3_free_table

Upvotes: 0

dalle
dalle

Reputation: 18507

You could combine them into a single statement:

select count(*), * from XXX where XXX

or

select count(*) as MYCOUNT, * from XXX where XXX

Upvotes: 1

Swaroop C H
Swaroop C H

Reputation: 17024

Once you already have the select * from XXX results, you can just find the array length in your program right?

Upvotes: 0

Related Questions