Sql COUNT function

[cnicolapc]

Hi,
I need to count records in a table in a SQL database, can you help me, you use the COUNT aggregate function? how?
Thank you in advance
Nicola

 

[Ohanian]

Dim intCount As Int
intCount = SQL1.ExecQuerySingleResult("SELECT count(*) FROM table1")

 

[cnicolapc]

Thank you very much
Nicola

 

[JTmartins]

Or you can

If there is some sort of autoincrement field as primary key you can

Dim i as Int
Dim SQL1 as sql

i=SQL1.ExecQuerySingleResult("SELECT MAX(autoincrement_fied) FROM tablename")

After this the variable i will carry the max number of the autoincrement field, which will be equal to the number of records.

I'm not sure wich one will be faster. But I use this method.

I know this is an old post, but I just came across it, so decided to give my 2 cents.

All the best

 

[klaus]

Be careful:
i = SQL1.ExecQuerySingleResult("SELECT count(*) FROM tablename")
and
i = SQL1.ExecQuerySingleResult("SELECT MAX(autoincrement_fied) FROM tablename")
are NOT the same.

Lets take an example:
You fill a databse with 10 entries.
SELECT count(*) returns 10, number of entries.
SELECT MAX(autoincrement_fied) returns 10, max value in the autoincrement column.

Now you delete 2 enrties.
SELECT count(*) returns 8, number of entries.
SELECT MAX(autoincrement_fied) returns 10, max value in the autoincrement column.

When you delete entries the autoincrement values are NOT changed !

Best regards.

 

[JTmartins]

Thanks

Thank you Klaus.

In my case, data is never deleted, so that situation never came across my mind. (inexperience)

But if it was, then I would have a serious problem.

Well...it apears my 2 cents...are actualy minus 2 cents

Or I may have saved someone of a future mistake like mine.

Jose