Select *, Count(*) in SQLite

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.

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 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'"

Get Count of rows with value true from a database in android kotlin

First correct your query like this:

val c = db!!.rawQuery("SELECT COUNT(*) FROM table WHERE value = 'true'"

because you don't want to group by value but count the rows of the tables that contain the value 'true'.

I assume that the column value contains strings.

If it contains booleans then the query should be:

val c = db!!.rawQuery("SELECT COUNT(*) FROM table WHERE value"

Then you can extract the value of the 1st and only column of the cursor by c.getInt(0):

Log.e("OUTPUT", c.getInt(0).toString())

What is the most efficient way to count rows in a table in SQLite?

The best way is to make sure that you run SELECT COUNT on a single column (SELECT COUNT(*) is slower) - but SELECT COUNT will always be the fastest way to get a count of things (the database optimizes the query internally).

If you check out the comments below, you can see arguments for why SELECT COUNT(1) is probably your best option.

Is count(*) constant time in SQLite, and if not what are alternatives?

SQLite has a special optimization for COUNT(*) without a WHERE clause, where it goes through the table's B-tree pages and counts entries without actually loading the records.
However, this still requires that all the table's data (except overflow pages for large records) is visited, so the runtime is still O(n).

SQLite does not store a separate record count in the database because that would make all changes slower.

---

Related Topics