Value Counter - Please Help

C

Cip Loera Jr

I need to count the number of occurances for data. I combine two fields to
somewhat give me a unique identifier. Cust Num and Amount. I want it to count
each value within that group

ie
These are the results I wishing to get

Unival Count
123456700.00 1
12341117800.90 1
12344445444.80 1
12344445444.80 2
2884444980.89 1
2884444980.89 2
2884444980.89 3
 
M

MGFoster

Cip said:
I need to count the number of occurances for data. I combine two fields to
somewhat give me a unique identifier. Cust Num and Amount. I want it to count
each value within that group

ie
These are the results I wishing to get

Unival Count
123456700.00 1
12341117800.90 1
12344445444.80 1
12344445444.80 2
2884444980.89 1
2884444980.89 2
2884444980.89 3
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Unless there is something you are not telling us, this is the probable
solution:

SELECT Unival, COUNT(*) As TotalCount
FROM table_name
GROUP BY Unival

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSW6apoechKqOuFEgEQI8iQCgsftLPO3c/nhMchspuOrgaqfSx8kAn0wl
FDBJOLB/Il7EjGQHs+mlZdZg
=KVLB
-----END PGP SIGNATURE-----
 
C

Cip Loera Jr

Thank you for your response,

I need each value to show the count, each instance of the value show what
the count is for it.

Value1 would show "1"
Value1 would show "2"
Value2 would show "1" since its the only one instance
Value3 would show "1"
Value3 would show "2"
Value3 would show "3"

My purpose is to get this to combine it with other fields that will somewhat
make it unique and would be able to compare to another source that has the
same info but may not have the same number of instances and will let me know
which those are.

Thanks again
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Oh, you want to create a sequence number. You'll need to create an
AutoNumber column in the table. In the following example the AutoNumber
column is named "line_num."


SELECT Unival, seq_nbr
FROM (
SELECT Unival, (SELECT COUNT(*)
FROM TableName
WHERE Unival = a.Unival
AND line_num <= a.line_num) As seq_nbr
FROM TableName As a)
ORDER BY Unival, seq_nbr

If you don't care about the sequence number being in ascending order you
can just use this:

SELECT Unival, (SELECT COUNT(*)
FROM TableName
WHERE Unival = a.Unival
AND line_num <= a.line_num) As seq_nbr
FROM TableName As a
ORDER BY Unival

The wrapping query sorts the seq_nbr in ascending order, otherwise the
sort order comes out descending, even when commanded to sort ascending -
wierd.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSW+dVYechKqOuFEgEQI95gCeJCMoT0J55wjIfFnSyono9y6VAHAAoIKf
L8ERQuhZX3KdWFHND2sWe1oY
=UFwp
-----END PGP SIGNATURE-----
 
Top