Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Record Count on Filtered Query

Posted on 2004-09-09
21
2,539 Views
Last Modified: 2010-04-05
I need to get the record count on a filtered query.

If I do:  Query1.RecordCount;

I get the un-filtered number. However I am filtering the query.

How do I get the record count for the filtered query?
0
Comment
Question by:VirtueTech
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12020902
Are you using a TQuery component? What database (paradox, other?)?

One should be careful to use the Recordcount as it could result in uncecessary traffic. Some datasets determine the recordcount by re-running the query. A better way is to include the count in your query and use a where clause instead of a filter. This way traffic is further reduced i.e. instead of loading all records and then filtering based on your needs, you filter before retrieving the records on the server side.

e.g.

Select EmployeeName, Count(EmployeeName) FROM Employees WHERE Department = 'ACCOUNTS'

Regards
Pierre
0
 

Author Comment

by:VirtueTech
ID: 12020921
Yes I'm using Tquery.

I basically want to show the count total of the query in a label I am refreshing.

How could I do this with your example? Thanks.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12020922
You should use another Query as:

function  GetRecCount: Integer;
var
  RCount: Integer;
begin
  Result := 0;
  Query2.Active := False;
  Query2.SQL.Text :=' SELECT COUNT(*) AS RCOUNT FROM FRIENDS WHERE'
                         +  ' FILTER CONDITION';
  Query2.Active := True;
  Result := Query2.FiledByName('RCOUNT').AsInteger;
end;

Just from Head
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:VirtueTech
ID: 12020950
That would work....though I don't really want to add another query to the load...if it is the only way then so be it.

Any other ideas before I use that one?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12020955
function  GetRecCount: Integer;
var
  RCount: Integer;
begin
  RCount := 0;
  try
    Query2.Active := False;
    Query2.SQL.Text :=' SELECT COUNT(*) AS RCOUNT FROM FRIENDS WHERE'
                           +  ' FILTER CONDITION'; // here is the filter condititon of Query1 in SQL format
    Query2.Active := True;
    RCount := Query2.FiledByName('RCOUNT').AsInteger;
  finally
    Result := RCount;
  end;
end;
0
 
LVL 12

Assisted Solution

by:esoftbg
esoftbg earned 100 total points
ID: 12020987
Depend of that what kind of Database use:
You may use a stored procedure and call it by TStoredProc component ....
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12020996
Say you have a grid listing all the employees, their department, phone numbers etc. you would do something like:

Query1.SQL.Text:= 'SELECT Count(*) AS NumRecs, EmpName, Dept, TelNum FROM Employees WHERE Dept = 'SomeDept';
Query1.Open;
(*If you linked this to the grid using a datasource, then opening the query would show the relevant records.*)
(*Obviously you wouldn't show the Numrecs field in the grid - you can select what you want to see using the grid's columns editor*)

In the Query1's AfterOpen event you type:

YourLabel.Caption:= Format('Number of Employees = %d', Query1['NumRecs']);
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021015
Sorry, Last line should have been:

YourLabel.Caption:= Format('Number of Employees = %d', [Query1['NumRecs']]);
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021037
If you really have to use filters, a better way to get the filtered record count would be:

function GetRecCount(DataSet: TDataset): integer;
var i: integer;
begin
  i:= 0;
  if Not Dataset.Active then DataSet.Open;
  DataSet.First;
  While Not Dataset.eof do
  begin
    Inc(i);
    Dataset.next;
  end;
  result:= i;
end;

This function will only count filtered records.
0
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 400 total points
ID: 12021065
It would also be a good idea to Disable controls that the dataset is linked to and return to the record selected before counting the records:

i.e.

function GetRecCount(DataSet: TDataset): integer;
var i: integer;
    BkMark: pointer;
begin
  i:= 0;
  if Not Dataset.Active then DataSet.Open;
  BkMark:= Dataset.GetBookmark;
  Dataset.DisableControls;
  DataSet.First;
  While Not Dataset.eof do
  begin
    Inc(i);
    Dataset.next;
  end;
  Dataset.GotoBookmark(BkMark);
  Dataset.EnableControls;
  result:= i;
end;
0
 

Author Comment

by:VirtueTech
ID: 12021384
I don't have a DataSet I have DataSource....do I swap them?
0
 

Author Comment

by:VirtueTech
ID: 12021408
DOH! Nevermind...that was dumb..excuse the newb
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12021456
why not:

Query1.Filtered := False;
i := Query1.RecordCount;
Query1.Filtered := True;
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021459
Yup... hehe

Your TQuery descends from TDataset, so you could just use as follows:

YourLabel.Caption:= Format('Number of Employees = %d', [GetRecCount(Query1)]);
0
 

Author Comment

by:VirtueTech
ID: 12021472
PierreC:  Your function, I used the second one, works but is kinda slow because it has to run the query again it seems.

Any idea on how to speed it up if possible? Thanks.
0
 

Author Comment

by:VirtueTech
ID: 12021683
select count(*) into recno, tenmstacs.name1,units.OCP1, units.unit, units.build#, unitextr.alpha30_1
FROM TENMSTACS,UNITS,UNITEXTR  
WHERE units.BUILD# = unitextr.BUILD# and units.UNIT = unitextr.UNIT and units.ocp1 = tenmstacs.tenant

It doesn't like this query. I'm using DB2 as my database.
0
 

Author Comment

by:VirtueTech
ID: 12021693
SORRY...it doesn't like this query...not the one above:

select count(*) asrecno, tenmstacs.name1,units.OCP1, units.unit, units.build#, unitextr.alpha30_1
FROM TENMSTACS,UNITS,UNITEXTR  
WHERE units.BUILD# = unitextr.BUILD# and units.UNIT = unitextr.UNIT and units.ocp1 = tenmstacs.tenant
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021698
I doesn't actually run the query again. It simply steps through all records if not filtered and all filtered records if filtered and counts them. Since these are already in memory, this should be pretty fast. How many records are we talking about?

You say you used the second GetRec function i.e. the one that Disables Controls first? If not, this could result in much slower results.


If you need it faster, then my first suggestion is the way to go i.e. use a select query that includes the count(*) AS Numrecs and use a where clause instead of using a filters. This should be the quickest.

As a matter of reference, I counted 50,000 records in 120 milliseconds on my Pentium 4 1.8GHz machine using one text field of length = 50; Seems pretty fsat to me. Tell me about your results...
0
 

Author Comment

by:VirtueTech
ID: 12021739
Nevermind...it was slow in DEBUG mode. When run in the compiled application it worked very fast,

Thanks PierreC...
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021744
I'm not familiar with DB2 but here are my observations:

1.) Missing space after AS:

SELECT COUNT(*) AS NumRecs

2.) Maybe DB2 doesn't like the *:

Just try using any other field e.g. Count(tenmstacs.name1) instead

0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12021784
Glad I could help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 84
Magic Software info 18 138
Tidtcpserver listening on multiports? 1 35
shape, triangle, dbctrlgrid 3 17
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question