Re: [sqlite] Counting rows

On Thu, 11 Dec 2014 15:19:26 + Simon Slavin wrote: > In my table which had about 300 million (sic.) rows I did this > > SELECT count(*) FROM myTable; > > to count the number of rows. After half an hour it was still > processing and I had to kill it. > > I know that

Re: [sqlite] Counting rows

>From previous reading (years ago on this list) I normally do select count(1) from tableName ; to count the rows in a table. as an alternate, select count(primary_key_or_SomeIndexName) from tableName when trying to get an actual count. beware: select count(someField) from table; will not count

Re: [sqlite] Counting rows

On 11 Dec 2014, at 4:39pm, Dominique Devienne wrote: > I have a little utility that connects to Oracle, and does a big UNION ALL > query to get the counts of all my tables (82 currently): Yeah, it's easy in Oracle. The problem is that SQLite3 uses a tree to store lists,

Re: [sqlite] Counting rows

On Thu, Dec 11, 2014 at 4:19 PM, Simon Slavin wrote: > In my table which had about 300 million (sic.) rows I did this > SELECT count(*) FROM myTable; > to count the number of rows. After half an hour it was still processing > and I had to kill it. > I have a little

Re: [sqlite] Counting rows

On 11 Dec 2014, at 3:58pm, Paul Sanderson wrote: > would count _rowid_ from mytable be quicker Hmm. Given that these tables have the normal use of rowid, and that rows in this table are only inserted, never deleted, I wonder whether SELECT max(rowid) FROM

Re: [sqlite] Counting rows

Simon Slavin wrote on Thursday, December 11, 2014 10:19 AM > I know that the internal structure of a table means that this number > isn't simple to produce. But is there really no faster way ? This > table is going to have about six times that amount soon. I really > can't count the rows in

Re: [sqlite] Counting rows

On 2014/12/11 17:19, Simon Slavin wrote: In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this

Re: [sqlite] Counting rows

would count _rowid_ from mytable be quicker Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite

[sqlite] Counting rows

In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this number isn't simple to produce. But is

Re: [sqlite] Counting rows in one table, and comparing COUNT with column in other table?

"Gilles Ganault" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > One more thing: I'd like to only retrieve rows where code.number and > COUNT(companies.code) don't match SELECT code.number, COUNT(companies.code) FROM code JOIN companies ON (code.id=companies.code) GROUP BY (code.id)

Re: [sqlite] Counting rows in one table, and comparing COUNT with column in other table?

On Tue, 11 Nov 2008 07:30:21 -0500, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >> SELECT code.number,COUNT(companies.code) FROM code,companies WHERE >> code.id=companies.code >> > >Add "GROUP BY code.id" One more thing: I'd like to only retrieve rows where code.number and

Re: [sqlite] Counting rows in one table, and comparing COUNT with column in other table?

On Tue, 11 Nov 2008 07:30:21 -0500, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >Add "GROUP BY code.id" Way to do. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Counting rows in one table, and comparing COUNT with column in other table?

"Gilles Ganault" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Here's an example of data in the CODE and COMPANIES tables: > > > CODE table > id=1 > number=26 > > COMPANIES table > id=x > code=1 > > > This doesn't: > > SELECT

[sqlite] Counting rows in one table, and comparing COUNT with column in other table?

Hello This is a newbie question. I could successfully retrieve the results by sending two queries and extracting data with Python, but I was wondering if I could perform the same action in one SQLite query. Here's an example of data in the CODE and COMPANIES tables: CODE table id=1

Re: [sqlite] Counting rows in multiple tables and joining on an index

create table t1 (rpsIndex INTEGER); create table t2 (rpsIndex INTEGER); create table t3 (rpsIndex INTEGER); insert into t1 values (1); insert into t1 values (1); insert into t1 values (2); insert into t2 values (1); insert into t2 values (2); insert into t2 values (3); insert into t3 values (3);

[sqlite] Counting rows in multiple tables and joining on an index

I have three tables, each of which has the column rpsIndex. This column is not a unique index/primary key. I would like to count the number of times each rpsIndex appears in all three tables. E.g. Table1 rpsIndex=1 rpsIndex=1 rpsIndex=2 Table2 rpsIndex=1 rpsIndex=2 rpsIndex=3 Table3 rpsIndex=3