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 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.
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.