Help with Sum

[MikeL91]

This is what I have:

select piecedesc,pieceid,county,litcode,litcode2,litcode3,phonecnt,nophonecnt,totalcnt, cnt(*) as qty from AgeinOutput ;
group by piecedesc,pieceid,county,litcode,litcode2,litcode3,phonecnt,nophonecnt,totalcnt into cursor mike


What I need is a report with all those fields, but I need Phonecnt to show the total for all records by that field, and the same for nophonecnt & totalcnt.

This is the data:
LITCODE PHONECNT NOPHONECNT TOTALCNT
LIT1 1 0 1
LIT1 1 0 1
LIT1 0 1 1
LIT2 0 1 1
LIT2 0 1 1


So this what I need the select to show:
LITCODE PHONECNT NOPHONECNT TOTALCNT
LIT1 2 1 3
LIT2 0 2 2

Confused?
Thanks in advance,
Mike

 

[Stella740pl]

Confused?

Not that much. What you need is grouping in the report on litcode. When in the report designer, select from menu Report->Data Grouping, and make your choices. Then design Group Footer band exactly as you would Detail band.

If you need to print only those summary lines, add SUMMARY keyword to your REPORT FORM command.
Or just do SUM(PHONECNT) AS PHONECNT, SUM(NOPHONECNT) AS NOPHONECNT and SUM(TOTALCNT) AS TOTALCNT right in your SELECT statement, and you wouldn't need the grouping.

Stella

 

[MikeL91]

Thank You Stella

 

[Olaf Doschke]

Well, why dont you:

select;
piecedesc,pieceid,county,;
litcode,litcode2,litcode3,;
sum(phonecnt) as phonecnt,;
sum(nophonecnt) as nophonecont,;
sum(totalcnt) as totalcnt,;
count(*) as qty;
from AgeinOutput ;
group by piecedesc,pieceid,county,;
litcode,litcode2,litcode3;
into cursor mike

And then you don't have to group in the report...

Bye, Olaf.

 

[Stella740pl]

Olaf, I mentioned that option, too - only for the case if detail lines don't have to be printed.

 

[Olaf Doschke]

Ah, I see. Sorry, Stella.