Sqlite Count Example

SELECT *, COUNT(*) in SQLite

SELECT *, COUNT(*) FROM my_table is not what you want, and it's not really valid SQL, you have to group by all the columns that's not an aggregate.

You'd want something like

SELECT somecolumn,someothercolumn, COUNT(*) 
   FROM my_table 
GROUP BY somecolumn,someothercolumn

SQLite - How to perform COUNT() with a WHERE condition?

SELECT
shelves._id AS _id,
shelves.shelf_name AS shelf_name,
COUNT(products._id) AS total_num_products_in_shelf,
sum(case when products.priority > 0 Then 1 else 0 end) 
as num_products_in_shelf_with_priority
FROM shelves INNER JOIN products
ON shelves._id = products.shelf_id
GROUP BY shelves._id, shelves.shelf_name
HAVING COUNT(products._id) > 0
ORDER BY shelf_name ASC

You can include a case condition and then sum it. Also, included is the shelf_name in the group by clause.

How to count values in an SQLite table using Python?

Did you try this :

 rowsQuery = "SELECT Count() FROM %s" % table
 cursor.execute(rowsQuery)
 numberOfRows = cursor.fetchone()[0]

you have to change the rowsQuery according to your need .

For your question:

 rowsQuery = "select count(STUDENT) from table_name where Gender= 'Male'"

How To Use SQLite COUNT in Android to return number of rows

I think the zero parameter is overriding the results

I have no idea what you think that this means.

When I remove the zero the code breaks

That is because getInt() needs to know the column of the Cursor to retrieve.

You are also crashing at runtime, as your SQL is invalid. Your SQL statement amounts to:

SELECT COUNT(foo) WHERE foo = left

(where foo is whatever TableData.TableInfo.DIRECTION in Java refers to)

Not only does your SQL statement lack a table to query against, but if left is supposed to be the value of a string column, you need to quote it. You will wind up with something like:

SELECT COUNT(foo) FROM tablename WHERE foo = 'left'

do I need to put a while loop around the query to move the cursor for a COUNT query?

No.

Is there another way of counting the rows where the string value is 'left' and the sum can be returned?

Not really, other than the fix that I outline above.

sqlite: how to get a count of group counts

As simply as adding another grouping above:

select event_count, count(*) as users_count
from
(select count(uid) as event_count
 from table
 group by uid) t
group by event_count
order by event_count

How do I count the number of rows returned in my SQLite reader in C#?

The DataReader runs lazily, so it doesn't pick up the entirety of the rowset before beginning. This leaves you with two choices:

1. Iterate through and count
2. Count in the SQL statement.

Because I'm more of a SQL guy, I'll do the count in the SQL statement:

cmd.CommandText = "select count(id) from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
int RowCount = 0;

RowCount = Convert.ToInt32(cmd.ExecuteScalar());

cmd.CommandText = "select id from myTable where word = '" + word + "';";
SQLiteDataReader reader = cmd.ExecuteReader();

//...

Note how I counted *, not id in the beginning. This is because count(id) will ignore id's, while count(*) will only ignore completely null rows. If you have no null id's, then use count(id) (it's a tad bit faster, depending on your table size).

Update: Changed to ExecuteScalar, and also count(id) based on comments.

---

Related Topics