Those who have non-normalized data in tables must suffer the consequences of their own flawed design & implementation.
I hope you started writing PL/SQL immediately after your original post to untangle this mess. This is the only alternative.
You should NEVER, EVER store multiple values in a single field/column.
This is a classic example of how to store data incorrectly.
Write a cursor to loop thru the rows.
For each row loop thru the pairs & insert KEYID, VALUE_CNT pair into a table.
After this table has been loaded with all the pairs then issue the following SQL for the two desired answers
SELECT KEYID, SUM(VALUE_CNT) FROM NEW_TABLE GROUP BY KEYID.
The number of rows returned is the number of DISTINCT KEYID which occur in original table.
Last edited by anacedent; 04-17-08 at 00:28.
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.