I just want the count, not the sum.

StationInfo has 3 records with a SiteID of 1001, but count returns 1.

I am stumped ...

Eric

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...

-----Original Message-----
From: Lindsay Gauton [mailto:lgauton@...]
Sent: Wednesday, April 14, 2004 11:31 AM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Trouble with Count(*)

Count is a count ie number of records

If you want the sum of a set of values then use sum

Ie.

Select sum(value) as NumStations from ....

Will sum the values in a given field

Lindsay

-----Original Message-----
From: Eric Tishler [mailto:etishler@...]
Sent: 14 April 2004 15:14
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Trouble with Count(*)

Thanks for your input Helen. Your syntax produced a SQL error unknown
token AS. Lindsay's syntax worked. I suppose the AS needs to be next to
the Count(*).

Unfortunately I only seem to get back counts of 0 or 1 and I know that
there are several cases where there is more than one station at a given
site.

The following table illustrates what I got back and what the real count
should be:

SiteID Count Returns Actual Value
--------- -------------------- -------------------
1001 0 3
1002 1 1
1003 0 0
1004 1 2
1005 1 1
1008 1 1
1009 1 1
1010 0 0
1011 1 1
1012 0 0

My code now looks like this:

var
sSiteID : string;
iNumStations : integer;
qrySiteInfo : TIBOQuery;
qryStationInfo : TIB_DSQL;
begin
// create the query objects
qrySiteInfo := TIBOQuery.Create(Application);
qryStationInfo := TIB_DSQL.Create(Application);

with qrySiteInfo do
begin
// prepare the query to get each site name
Active := False;
SQL.Clear;
SQL.Add('SELECT "SiteID" from "GeneralOptions"');

// run the query, open the DataSet
qrySiteInfo.Prepare;
qrySiteInfo.ExecSQL;
Open; First;

// loop to process each site record
while not Eof do
begin
// get the SiteID
sSiteID := FieldByName( 'SiteID' ).AsString;

with qryStationInfo do
begin
// prepare query to get number of stations at current site
Active := False;
SQL.Clear;

SQL.Add('SELECT COUNT(*) AS "NumStations"');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');
ParamByName('SiteID').AsString := sSiteID;

// run the query, open the DataSet
Unprepare;
Prepare;
Execute;

// get the number of stations at current site
iNumStations := FieldByName('NumStations').AsInteger;

Inspecting the value of iNumStations I get the values shown in the table
above. Any ideas on what I am doing wrong here?

Thank you,

Eric

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...

-----Original Message-----
From: Lindsay Gauton [mailto:lgauton@...]
Sent: Wednesday, April 14, 2004 10:23 AM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Trouble with Count(*)

Use

SELECT Count(*) as NumStations from ....


Lindsay

-----Original Message-----
From: Eric Tishler [mailto:etishler@...]
Sent: 14 April 2004 14:05
To: IBObjects@yahoogroups.com
Subject: [IBO] Trouble with Count(*)

Hi,

I am a bit of a neophyte with SQL ...

I am trying to get the count for a number of records matching certain
criteria.

I looked in the LANGREF.PDF that comes with and the syntax shows either:

SELECT COUNT(*) INTO :field FROM TABLE WHERE condition;

Or

SELECT COUNT(*)FROM TABLE WHERE condition INTO :field;

In my actual Delphi (6) code using IBObjects I have:

qrySiteInfo := TIBOQuery.Create(Application);
qryStationInfo := TIBOQuery.Create(Application);

with qrySiteInfo do
begin
// prepare the query to get each site name
Active := False;
SQL.Clear;
SQL.Add('SELECT "SiteID" from "Sites"');

// run the query, open the DataSet
Active := True; Open; First;

// loop to process each site record
while not Eof do
begin
// get the SiteID
sSiteID := FieldByName( 'SiteID' ).AsString;

with qryStationInfo do
begin
// prepare query to get number of stations at current site
Active := False;
SQL.Clear;
SQL.Add('SELECT Count(*)');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');
SQL.Add(' INTO :NumStations');

ParamByName('SiteID').AsString := sSiteID;

// run the query, open the DataSet
Active := True; Open; First;

But I get an SQL error (Invalid token INTO line 4, char 2) when I go to
execute the ParamByName statement below the SQL.Add statements

I have also tried alternate SQL syntax as shown below. But they all
produce the same error. Ideally I want to get the count for the number
of records that match the SQL statement I am having problems with.

// alternate #1
SQL.Add('SELECT Count(*)');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');
SQL.Add(' INTO "NumStations"');

// alternate #2
SQL.Add('SELECT Count(*) INTO :NumStations');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');

// alternate #3
SQL.Add('SELECT Count(*) INTO "NumStations"');
SQL.Add(' FROM "StationInfo"');
SQL.Add(' WHERE "SiteID" = :SiteID AND "ActivateStation" = 1');

Can somebody help me with this?

Thank you,

Eric

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...



[Non-text portions of this message have been removed]



________________________________________________________________________
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !

Yahoo! Groups Links





---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004




________________________________________________________________________
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !

Yahoo! Groups Links









________________________________________________________________________
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !

Yahoo! Groups Links





---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004





___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links