SQL Aggregate functions

[phaworth]

Having trouble getting SQL aggregate functions like COUNT and SUM to work.

A statement like the following always results in zero:

Code: Select all

put revdb_execute(gDBID,"SELECT COUNT(*) FROM Sales WHERE ProdID = 35") into field "SalesCount"

If I execute the same statement in my SQLIte admin tool, I get a non-zero result.

Same symptoms if I use other functions like SUM.

I think I'm seeing why this happens since the dictionaty says using revdb_execute is the same as using revexecuteSQL, which in turn claims to return the number of rows operated on for INSERT, UPDATE, and DELETE commands and zero for everything else. If that's the case, what command/function should I use to get the results I'm looking for?

Thanks,
Pete

[Janschenkel]

The revdb_execute function is meant for INSERT, UPDATE and DELETE commands, not for SELECT queries. Use the revdb_querylist function instead

Code: Select all

put revdb_querylist(return,tab,gDBID,"SELECT COUNT(*) FROM Sales WHERE ProdID = 35") into field "SalesCount"

Jan Schenkel.

[phaworth]

Thanks Jan, I figured it out after I posted.