TQuery, counting, adding and so on

[wizzor]

Hi,
I'm making a rather simple database program, with TurboDelphi. I would like to make some calculations, based on some DB data, provided by a TQuery component over ODBC.

I would like to do the following:
Count rows that were returned by the query.
The only way I found that could do this, is the RecordCount method. Is this "The Way" or is there a better one?
Count rows that have the same value on a column.
Count into one variable, numeric (real) values of one column.

I can figure out several workarounds to the two last ones, using select statements and loops, but I do have the feeling multiple SQL queries are not the most efficient way to go about this.

Anyone care to hint?

 

[lespaul]

Use the SQL COUNT and then assign the results to a variable:

with SomeQuery do
begin
SQL.Clear;
SQL.Add('SELECT COUNT(*) As NumberOfRecords FROM tableName WHERE SomeField = ''SomeCondition''');
Active := True;
SomeVariable := FieldByName('NumberOfRecords').AsInteger;
end;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Tony1l]

Try by defining a calculated field.

Right Click on the Tquery component and define all your fields from the Fields Editor. When you use this you must define all fields not just new ones.

Here's a loose example

var
VarName:=Integer;

Begin
TQuery1.FieldByName('RowNumber').AsString:=IntToStr(VarName);
inc(VarName);

Tony

 

[wizzor]

Thank you for the answers thus far, I'm afraid I don't understand the latter.

What exactly is a 'calculated field'?

In the code example, why are there both, AsString and then IntToStr?

As for the first one, it works just fine, thanks!

 

[Tony1l]

1) Right-click Tquery and Add All Fields then do this again and select NewField give it a name ie: 'RowNumber'define it's type as an Integer and select CalcField.
2) On your TQuery add an 'OnCalcFields' Event.
3) Code something similar to this..
Tquery1.FieldByName('RowNumber').AsInteger:=i;
inc(i); //i is global integer

This example should take some load off the Database server.

Tony

 

[Opieo]

Forgive my youthful ignorace, but why not just use the Query.RecordCount?
I am not understanding how the other methods are more efficient?
Even if you had to track a total you could just do
Total := Total + Query.RecordCount
each time you ran the query.
Please explain.

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[lespaul]

The database that I connect to the Query.RecordCount doesn't work. I always get -1.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ThunderForest]

Also see thread

http://www.tek-tips.com/viewthread.cfm?qid=1375844

Getting answers before I'm asked.
Providing answers if I can.

 

[roo0047]

LOL @ Thunder - How did I know where that link pointed to before clicking it?!?

As we know, most all SQL components are just a wrapper or interface to a dll. IF the author implemented RecordCount, it will always work; if not then it's an abstract method and will (or should) always return -1. So far, it's worked for me. In the rare situations were it doesn't, use SQL function Count().

In the rarer (? more rare) situations where your SQL languages does not support Count() AND your component does not support RecordCount, then you're going to have to iterate through all the records and count them yourself.
All agreed?

Roo
Delphi Rules!

 

[wizzor]

Well, in my case, record.count works, so no problems there, just didn't know it existed

Anyway, any thoughts on this, counting the contents of the fields thing?

 

[roo0047]

Well, in my case, record.count works, so no problems there, just didn't know it existed

Its not record.count - it's query.recordcount

Anyway, any thoughts on this, counting the contents of the fields thing?

You want to count the number of fields or add up the numeric values of a particular column?

Look at sql function Sum(). (I'm still trying to get that one working for me )

Roo
Delphi Rules!

 

[lespaul]

I use SUM in my queries all the time...what kind of problems do you have using it?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[wizzor]

Hey, I solved it!
I'm gonna look into that sum() thing, not that I'd use it now (now that I got this working), but just to learn.

form1.InsertQuery.SQL.Clear;
form1.InsertQuery.SQL.Add('SELECT * FROM tbl_sales WHERE history = ''0''AND ticket = ''adult''');
form1.InsertQuery.open;

apuint := form1.InsertQuery.RecordCount;
repeat


    
sales := sales+strToFloat(form1.InsertQuery.FieldValues['price']) ;
form1.InsertQuery.FindNext;
apuint := apuint-1;

until(apuint<=0);

Again, I want to query the db server as little as possible, since the connection is rather slow over the Internet.

 

[whosrdaddy]

never use recordcount for your loops


do something like this:

form1.InsertQuery.SQL.Clear;
form1.InsertQuery.SQL.Add('SELECT * FROM tbl_sales WHERE history = ''0''AND ticket = ''adult''');
form1.InsertQuery.open;
form1.InsertQuery.First;
while not form1.InsertQuery.Eof do
 begin  
  sales := sales+strToFloat(form1.InsertQuery.FieldValues['price']) ;
 form1.InsertQuery.Next;
end;

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[lespaul]

Here's how you would mess around with the SUM for this query and assign the sum of all the records that meet your criteria to the variable sales:

form1.InsertQuery.SQL.Clear;
form1.InsertQuery.SQL.Add('SELECT SUM(Price) As TotalPrice FROM tbl_sales WHERE history = ''0''AND ticket = ''adult''');
form1.InsertQuery.open;

sales := form1.InsertQuery.FieldByName('TotalPrice').AsFloat

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Opieo]

@The Who

Why never use record count for loops?
Please explain the reasoning as I am very very interested.

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[whosrdaddy]

like stated recordcount is not always working (depends on the db you're connecting to).

that's why I like the more generic approach using EOF.

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[wizzor]

Ah, you're saying that I might end up with an indefinite loop, or no loop at all, (resulting in NULL values on my calculations) if I use recordCount as a means of getting the loop limiter.

Even though it sounds strange, that the function working or not, was somehow connected to the type of the database used, since counting the operation is (afaik) done locally by the TQuery object, not by the DB.
It would be interesting to conduct some tests on some servers other than MySQL.

 

[Opieo]

Daddy,
I just thought there might be something else to it.
Thank you though. I will keep it in mind.
It just happens to work with my current DB program =P
But the generic idea is better.
If I ever get some spare time (at work .... not likely) I will probably go back and generalize it just to promote interoperability.

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[whosrdaddy]

Now I remember the main reason why I don't use recordcount.

if you set the cursorlocation to clUseServer ,which means that the dataset won't cache any data locally, recordcount will return -1, and this because recordcount only works on the locally cached data. I have a few projects where they run a multiuser environment where everyone is updating the same database, thats when I use the server cursor because they need to see each other's updates...

so, if you use the EOF method your code will work in both cases...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!