Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    41

    Unanswered: Split String - Distinct count, SUM

    Hi,



    I’ve a problem on hand that I am unable to resolve – Any help would be appreciated.



    I’ve a value in a field that is 1500 byte long and comes like the following. First set of numbers is a key (prior to semi-colon) and value after a hyphen (-) is a count for that key. Each set of keys and their counts are separated by two semi-colons ( ;; ) except when there exists only 1 key. I am looking for a script that will tell me distinct count of keys and total sum of count. There are several thousand rows like this in a table.



    4148930323030;-1;;2739290220002;-1;;2739290220002;-2;;2739290220002;-2



    Pls help.



    Thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •