[SQLite] How to get the number of…

I used two different (not so much) sql commands to get the total of Records before (and including) 2018-12-31 and 2017-12-31, then using the same criteria, I asked how many men (and compute how many women).

In the first case, I used: SELECT * following with the TABLE name and the criteria,
and in the other case, I used SELECT COUNT(*) + same as above = the check for Men in the correct column, an get an eye in the debugger for the value, to compare with the first result.

To get the value for the first case, I used Total_In = rs.RecordCount.

The question is: I get the same result, but which one must I keep ?
For user safety, I display the list of Records for the first case (useless in the second case since it isthe same information (Men only vs Men and Women)… (so the user can check by itself if (s)he does not trust the computer !).

select count(*) and rs.idxfield(1).integervalue

and you could do all the requests in ONE select statement.

If you need all the rows to do something (display them) you should use “RecordCount”. If you just need the count use “select count(id)”.

SELECT  SUM(totalCount) as totalcount, ,SUM(Count_Men) as count_men,SUM(Count_Women) as count_woment  {
FROM (
SELECT count(*) as totalCount,0 as count_men,0 as count_women from myTABLE
UNION
SELECT 0 as totalCount,count(*) as count_men,0 as count_women from myTABLE where gender="M"
UNION
SELECT 0 as totalCount,0 as count_men,count(*) as count_women from myTABLE where gender="F"
)

[quote=424508:@Dave S] SELECT SUBM(totalCount) as totalcount, ,SUM(Count_Men) as count_men,SUM(Count_Women) as count_woment { FROM ( SELECT count(*) as totalCount,0 as count_men,0 as count_women from myTABLE UNION SELECT 0 as totalCount,count(*) as count_men,0 as count_women from myTABLE where gender="M" UNION SELECT 0 as totalCount,0 as count_men,count(*) as count_women from myTABLE where gender="F" ) [/quote]

Hmm, or just

select count(id) as amount, gender from people group by gender

normally on dbs it is much better to use many small queries than one all-in-one query.
On some systems, we could handle some million queries / second but with only some slow queries we could take it easily down.

depends if you want 3 records or just one now doesn’t it

and for the record… RECORDCOUNT does work for SQLite, but may or may not work for other database engines

Thanks all.

The db file holds less than 2,000 Records.

I do not know (yet) what the client want (only the integer values or the RecordSet), once this informaton will come (tomorrow), I will do accordingly.

For the time being, the DB will stay to SQLite, but who knows…

On the other hand, your answers opened some doors I do not expected, thank you.

This change in the application started with a long wait (thinking how to do what I had to do) and then ended very fast until I was asking me (nd you) this question. Not so often.

The count(*) formulation for getting number of records, according to posts on the SQLite Users’ Mailing List, is optimised for speed.