Count of records

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

October 30, 2007, 05:03 PM

<msam>

Hi Praire,
I need to build the Count Column ..

MSAM

October 30, 2007, 05:07 PM

Prarie

what it the criteria to have a count?

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

October 30, 2007, 05:12 PM

Leah

quote:
PA PV PS PE Count
-----------------------
A 1 0 2001 1
A 1 0 2001

Assumption you are sorting on the PA column

PRINT PV PS PE
COMPUTE COUNTPA/I5S = IF PA EQ LAST PA THEN 0 ELSE 1; AS 'COUNT'
ON TABLE COLUM-TOTAL AS 'TOTAL COUNT:'

Leah

October 30, 2007, 05:13 PM

<msam>

well it is checking all the distinct values from the first 4 coulms as shown and based on that I need a Count

MSAM

October 30, 2007, 05:37 PM

Leah

quote:
PRINT PV PS PE
COMPUTE COUNTPA/I5S = IF PA EQ LAST PA THEN 0 ELSE 1; AS 'COUNT'
ON TABLE COLUM-TOTAL AS 'TOTAL COUNT:'

Then you need to sort on all and manipulate the compute as shown above, it currently looks at distinct values of PA

Leah

October 31, 2007, 08:33 AM

TexasStingray

Here is what I would do

TABLE FILE .....
PRINT PV PS PE
AND COMPUTE MYCNTR/I9 = IF PV EQ LAST PV AND PS EQ LAST PS AND PE EQ LAST PE THEN 0 ELSE 1; AS 'Count'
BY PV NOPRINT
BY PS NOPRINT
BY PE NOPRINT
ON TABLE SUBFOOT
"Total Count: <TOT.MYCNTR "
...
END

Scott

October 31, 2007, 09:54 AM

<msam>

Thanks Leah And TexasStringay For the support !!

October 31, 2007, 10:57 AM

Leah

You are most welcome.

Leah

November 06, 2007, 12:43 AM

johney

Hi,

Is there any similar command in Webfocus as that of COUNT(*) in oracle ? I need to get the total record count and if the count is 0 I need to do some other branching.

Can any body help me ?

Regards,
Johney.

Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF

November 06, 2007, 02:38 AM

Tony A

Johney,

Of course -

TABLE FILE filename
WRITE CNT.fieldname
......

Be sure to check the SQL produced (using the method that Francis has posted so many times before), to ensure that you do not end up issuing a request that results in a table space scan

... or use SQL passthru.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

November 06, 2007, 08:32 AM

PBrightwell

You can also do:
TABLE FILE CAR
COUNT *
END
If you are wanting to do conditional branching when there are no records, you might want to place a -RUN command after your request and then
-IF &LINES EQ 0 GOTO myroutine;

This message has been edited. Last edited by: PBrightwell,

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

November 06, 2007, 10:01 AM

Tony A

PB,

You have to be mindful of the SQL being produced in the background and therefore being passed to the RDBMS.

For instance, you would think that SUM CNT.field, WRITE CNT.field or COUNT * would all produce the same SQL as they are basically synonyms. Well they haven't always.

WRITE CNT.field was the only method of ensuring COUNT(*) being produced in the MVS DB2 interface at one time, hence the suggestion that the SQL is trapped before running in anger to ensure that no table space scan would be performed in getting the results.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

November 06, 2007, 10:10 AM

Tony A

Just out of interest, I have just run the four main methods against an Oracle source and got the following SQL -

SUM CNT.fieldname - SELECT COUNT(T1.fieldname) FROM tablename T1

ADD CNT.fieldname - SELECT COUNT(T1.fieldname) FROM tablename T1

WRITE CNT.fieldname - SELECT COUNT(T1.fieldname) FROM tablename T1

COUNT * - SELECT COUNT(T1.mainkeyfield) FROM tablename T1

Worth checking though

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

November 06, 2007, 02:39 PM

susannah

msam
have you experimented with count distinct ?
SUM CNT.DST.PA
which gives you the count of the distincly different values in your PA field, which seems to be what you're doing.
-s.

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID