Solved

Record Count on Filtered Query

Posted on 2004-09-09
21
2,373 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
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 gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now