Total Count HELP

[Dungi]

Hi all,
I cant seem to find the function COUNT in Progress...is NUM-RESULTS the progress version of COUNT?
If so, can i get an example as how to use it...

I am trying to display the totals as follows;
Number - Name - Tot Active - Tot Completed - Total
01 - Man 1 - 01 - 05 - 06
02 - Man 2 - 03 - 05 - 08

Thanks

 

[DevTeam]

Hi,

You can do this in your 4GL code through SQL count(*) :

DEF VAR myVar AS INT NO-UNDO.
SELECT COUNT(*) INTO myVar FROM mytable.

but that's SQL in 4GL, which is not recommended.

You can do something in 4GL this way :

DEF VAR myVar AS INT NO-UNDO.
FIND FIRST myTable NO-LOCK NO-ERROR.
DO WHILE AVAILABLE myTable :
    myVar = myVar + 1.
    FIND NEXT myTable NO-LOCK NO-ERROR.
END.

I think the NUM-RESULTS clause is available with QUERY syntax, something like that :

DEF QUERY myQuery FOR myTable.
OPEN QUERY myQuery PRESELECT EACH myTable.
DISP NUM-RESULTS("myQuery ").

 

[lord_icon]

Abl

In ABL, this is just +.
E.g. value1 + value2 = result

 

[DevTeam]

Re: Abl

In ABL, this is just +.
E.g. value1 + value2 = result

It depends on whether "Tot active" and "Tot completed" are fields or not... If not (=> agregate), he'll have to do the equivalent of the "GROUP BY" clause available in SQL... I'm not sure of the 4GL equivalent, maybe BREAY BY ?

 

[Dungi]

the data is kept as follows;

id name active complated
01 man1 A no
01 man1 I yes
01 man1 I yes
01 man1 I yes
01 man1 I yes
01 man1 I yes
...
...

i can display as shown above with the total at the bottom...

but i want to count the active contracts and the completed contracts and display on 1 row.

any ideas ?

 

[Casper]

You can do a break by:

It seems that active stands for not completed so with the data you gave active and completed are redundant fields. So I only checked on completed (which seems a logical field to decide wether something is active or not).

I also assume ID is an indexed field and name is just a description of ID.

define variable iActive as integer no-undo.
define variable iCompleted as integer no-undo.
 
for each table break by(Id):
    if first-of(Id)
    then do:
        /* reset */
       assign iActive = 0
                iCompleted = 0.
    end.
 
    if Completed then iCompleted = iCompleted + 1.
    else assign iActive = iActive + 1.
 
    if last-of(ID)
    then display id name iActive iCompleted (iActive + iCompleted).
end.

HTH,
Casper

 

[DevTeam]

What wasn't clear in what I wrote ?

 

[Casper]

same applies here.
You want your set of data represented as 1 row with total counts for active, completed and total per group.

The program I wrote does that.

if you have:

01 man1 A no
01 man1 I yes
01 man1 I yes
01 man1 I yes
01 man1 I yes
01 man1 I yes

then the display will show:

id name active completed total
01 man1 1 5 6

That's what you asked:

I am trying to display the totals as follows;
Number - Name - Tot Active - Tot Completed - Total
01 - Man 1 - 01 - 05 - 06
02 - Man 2 - 03 - 05 - 08

did you try out what I gave you?

Casper

 

[tamhas]

You might also want to look up the Accumulate functions.