Counter via a query?

[Karin J]

I would like to create a field with running total of the number of
occurences of each value (I have to do this to work with a legacy
system that expects data like this). I could do this programmatically
looping through the recordset, but wondered if it was possible via
SQL?
So if the field of interest contained letters of the alphabet:
A
B
B
C
B
C

I would like to generate a second field thus:
A 1
B 1
B 2
C 1
B 3
C 2 etc
(it actually doesn't matter "which" B is labelled 1 or 2)

Is this possible via SQL? I thought of generating a count for each
value and doing something via a series of UNION queries with a WHERE
clause based on the count, but highest count is 17 occurences, which
would mean 17 queries unioned together, and that would be a bit
clumsy.

Thankyou
Karin

 

[John Winterbottom]

Use a subquery. First though your table must have a primary key, (as all
tables should). If you have a table like this

create table foo
(
id Counter not null primary key,
myField varchar(50) not null
)

then you can get a rowcount over each myField group with

select f.id, f.myField
(
select count(*) from foo as f2
where f2.myField = f.myField
and f2.id <= f.id
) as rowCount
from foo as f
order by f.myField, f.id

 

[Marshall Barton]

I would like to create a field with running total of the number of
occurences of each value (I have to do this to work with a legacy
system that expects data like this). I could do this programmatically
looping through the recordset, but wondered if it was possible via
SQL?
So if the field of interest contained letters of the alphabet:
A
B
B
C
B
C

I would like to generate a second field thus:
A 1
B 1
B 2
C 1
B 3
C 2 etc
(it actually doesn't matter "which" B is labelled 1 or 2)

[]

It can be done in a single query by using a subquery, but
only if the records have a field that can be used to sort
the records in a unique order:

SELECT fl, (Select Count(*)
FROM table As X
WHERE X.fl = table.fl
And X.sortfield <= table.sortfield
) As Occurs
FROM table

If you can not define a unique sort order for the records,
then I think you will have to resort to the recordset
approach.

 

[Karin J]

John Winterbottom and Marshall Barton have almost simultaneously
posted the same, useful solution based on a subquery.

Thanks to you both from the bottom of my heart!

Karin