Running Counts/Totals

[rhinok]

Hi,

Sorry to ask a newbie question, but I'm not able to accomplish what I want within Crystal Reports (specifically, I'm trying to create a chart off running totals that were created in CR8, but its glitchy)...

How do I create a running count of records within my view?

Example:

Select
fieldname
runningcountfield
From
tablename
Group By
Field name

Result:

fieldname runningcount
fielda 1
fieldb 2
fieldb 2
fieldc 3

 

[stefanhei]

Hi.
Have a look at the ROWNUM - pseudocolumn:

SELECT fieldname,rownum
FROM tablename

Stefan

 

[sem]

Select
fieldname
, count(*) runningcountfield
From
tablename
Group By
Fieldname

 

[rhinok]

Ok, maybe for a bit of clarification:

This is my current SQL Statement:

Select
fieldname,
Count(fieldname) runningcountfield
From
tablename
Group By
Field name

Example:

fieldname runningcount
YES 1
YES 2
NO 2
YES 3

What I'd like to see in my results table is an incremented count that is dependant on the value of fieldname. If fieldname = 'Yes' then increment the count by one. If fieldname = 'No' then keep the count static until the next 'Yes' value.

Unfortunately, the statement above only gives me a single row count instead of a running count. I've looked at Group by Rollup, however, I don't want all those extra rows and it doesn't really give a true running count per record. Instead it sums up the count per rolled up group.

Rownum will just give me the number of each row, irrespective of the incremented, conditional running count I need.

Any other suggestions?

 

[sem]

You may use
select sum(decode(fieldname, 'YES',1,0)) from .. group by ..

Could you please write your example query a bit more precise?
Group By Field Name can not work because your query does not contain any references to Field or Name. I suppose you're grouping by some field other then fieldname, that, according to your example, contains only yes/no. Assuming this I can not write exactly from/group by clauses

 

[rhinok]

Following is my SQL Statement:

SELECT
A.FIELD_A,
A.FIELD_B,
A.FIELD_c,
A.FIELD_D,
A.FIELD_E,
A.FIELD_F,
A.FIELD_G,
A.FIELD_H,
A.FIELD_I,
B.FIELD_J,
DECODE(B.FIELD_K,'FIELD_VALUE','YES','NO') FIELD_K,
DECODE(B.FIELD_K,'FIELD_VALUE',D.FIELD_L,'') FIELD_L,
SUM(DECODE(B.FIELD_K,'FIELD_VALUE',1,0)) RUNNING_COUNT
FROM
TABLE_A A,
TABLE_B B,
TABLE_C C,
TABLE_D D
WHERE
--TABLE JOINS ONLY, NOT RELEVANT
GROUP BY
A.FIELD_A,
A.FIELD_B,
A.FIELD_c,
A.FIELD_D,
A.FIELD_E,
A.FIELD_F,
A.FIELD_G,
A.FIELD_H,
A.FIELD_I,
B.FIELD_J,
DECODE(B.FIELD_K,'FIELD_VALUE','YES','NO'),
DECODE(B.FIELD_K,'FIELD_VALUE',D.FIELD_L,'')

The problem here is that the Running_Count field only lists the single Row Sum, as opposed to an incremented "Running" sum which is what I really need. A colleague of mine just turned me onto analytic functions, however, I haven't been able to make them work with a Decode Statement.

 

[rhinok]

Ok, was able to resolve one of my two issues via Analytic Functions, however, my next challenge is to create a running distinct count. So far, I've only been able to generate a distinct count per group as opposed to a running distinct count that increments per group.

Thanks for all your help