Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Record Count on Filtered Query

Posted on 2004-09-09
21
Medium Priority
?
3,026 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 400 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 1600 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

577 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