Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2016
    Posts
    3

    Answered: please help to calculate A/B

    Hi to everyone

    i have a question about calculating with cells values

    have a simple table

    id (int), amount (int), share(text), value(int)

    for ex:
    1, 1000, "1/1", ????
    2, 2000, "1/20" , ????
    3, 100, "1/2", ????

    what i wish is to calculate missing value
    1000 * (1/1) = 1000
    2000 * (1/20) = 100
    100 * (1/2) = 50

    etc

    table format is fixed,i could not change anything, format,type etc... only what i could do is to update "VALUE" field for some other calculation

    please help me with this

    ty

  2. Best Answer
    Posted by bstjean

    "SELECT
    id, amount, share, (amount *
    CAST(SUBSTRING(share, 1, INSTR(share, "/")-1) AS UNSIGNED INTEGER))/
    CAST(SUBSTRING(share FROM INSTR(share, "/")+1 FOR (LENGTH(share)-INSTR(share, "/"))) AS UNSIGNED INTEGER) as value
    FROM MyTable;

    This will work for any numerator & denominator. You can try with weird shares like "5432/191" and "17/2341".

    I suggest you create a VIEW and never have to deal with that kind of crap ever after!!!"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so lets get this straight....
    because some (*&^&*^G has chose to represent a number as a text value it makes it tricky to use the numeric value. the design cannot be changed, but it sure looks real purdy.....

    there are possible workarounds this frightmare
    first and foremost... do the job properly, ignore the leave it alone diktat and save the 'share' column as numeric.
    alternatively, and this depends on the data is parse the text
    ..so if ALL your shares are 1 in x, and expressed as 1/x
    eg:-
    Code:
    SELECT id, amount, share,  amount * ( 1 / substring(share, 3, length(share)-2)) as proportion
    from mytable
    So the next phase could be to handle the leading term, instead of always presuming its 1.
    Code:
    select id,amount,share,  amount*(substring(share,1,instr(share,"/")-1)/substring(share,instr(share,"/")+1,length(share)-instr(share,"/"))) as proportion
    from mytable
    this is not the most efficient way of doing thigns, but then again whoever designed this didnt' relly give a fig about efficiency in the first place so maybe its good enough

    ...but frankly its starting to get unmanageable
    the above code will fail if say there is no value, or no /
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2016
    Posts
    3

    ok, will try this

    Quote Originally Posted by healdem View Post
    so lets get this straight....

    this is not the most efficient way of doing thigns, but then again whoever designed this didnt' relly give a fig about efficiency in the first place so maybe its good enough
    ...but frankly its starting to get unmanageable
    the above code will fail if say there is no value, or no /
    Thank you, i will test it tomorrow and post result

    and, yes, you are right. First number is not always 1, it could be 5000/20

    it is table that represent share ownership of plow land generated by some old government program, and that is it, no change, no modify

    thank you again

  5. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    222
    Provided Answers: 1
    SELECT
    id, amount, share, (amount *
    CAST(SUBSTRING(share, 1, INSTR(share, "/")-1) AS UNSIGNED INTEGER))/
    CAST(SUBSTRING(share FROM INSTR(share, "/")+1 FOR (LENGTH(share)-INSTR(share, "/"))) AS UNSIGNED INTEGER) as value
    FROM MyTable;

    This will work for any numerator & denominator. You can try with weird shares like "5432/191" and "17/2341".

    I suggest you create a VIEW and never have to deal with that kind of crap ever after!!!

  6. #5
    Join Date
    Mar 2016
    Posts
    3

    Thumbs up WOOOOW it's work

    Thank you healdem , bstjean

    Both of solutions work !!!

    Heladem query take about 1 sec on 100K rows
    and Bstjean query finished in 0.8 sec on 100K rows

    anyway, it is fantastic

    thank you again

Tags for this Thread

Posting Permissions

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