Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    35

    Unanswered: SQL complex query

    Hi to all,

    I have created a table named SCORE and inserted values(overs) in it, just to calculate the approximate over.

    For example, we all know There are 6 balls per over . But i need the approxiamte over for the table;

    Example;

    1) If there is 26.8(27 overs 2 balls remaining) overs,then the approximate over is 27.2 .
    2) If it was 20.7, then it will be 21.1 overs.

    Pls suggest me a query to calculate the approximate over for table.
    Last edited by satheeshsharma; 05-02-17 at 02:50. Reason: word addition

  2. #2
    Join Date
    Jun 2004
    Posts
    812
    Provided Answers: 1
    Your example is puzzling me.
    You state looking for an approximate value, yet your required answer is specific (i.e. "27.2"). Do you want an approximate value? If so, what are the approximation rules? Or do you want a specific value (as shown in your examples)?
    You want you to calculate how many overs & balls are left, but how are we supposed to know how many overs there are in total? Both your examples are using different values.
    We also need to know your table structure, and some sample data. Saying that you created a table & dumped some data in it doesn't help when trying to write queries based on the table.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    May 2013
    Posts
    35
    SCORE: Tablename

    Overs : Single column
    --------
    10.6
    12.5
    8.6

    Here the sum of values is sum(overs) = 31.7

    But the approximate Over is 32.1(32 overs and 1 one ball). I need this as a result.

    I need exact query for this.

    This is an interview question asked by a interviewer with me.

  4. #4
    Join Date
    Mar 2007
    Posts
    627
    Quote Originally Posted by satheeshsharma View Post
    Here the sum of values is sum(overs) = 31.7
    But the approximate Over is 32.1(32 overs and 1 one ball). I need this as a result.
    Why 32? Why 1 ball, as "we all know There are 6 balls per over" an the table has three rows?
    Quote Originally Posted by satheeshsharma View Post
    I need exact query for this.
    Code:
    select ceil(sum(overs))+1/10 from score;
    or (that will return even previously stated results)
    Code:
    select sum(overs)+4/10 from score;
    or simply
    Code:
    select 32+1/10 from dual;
    or ... (you may guess any other rules leading to the result about).
    Quote Originally Posted by satheeshsharma View Post
    This is an interview question asked by a interviewer with me.
    Maybe the interviewer should describe clearly the rules leading the result set.
    From your posts, it is impossible to deduce them.

    [Edit: floor -> ceil]
    Last edited by flyboy; 05-02-17 at 06:18.

  5. #5
    Join Date
    Jun 2004
    Posts
    812
    Provided Answers: 1
    Definition of "Approximate": close to the actual, but not completely accurate or exact.

    Example: Taking the value '12.5' in your table -
    An approximate value could be 12 (if you decide to round down to the nearest whole number), or 13 (if you decide to round up to the nearest whole number).

    I suspect that you are not actually looking for the approximate
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Jun 2017
    Location
    Ukraine
    Posts
    11

    SQL complex query

    We checked and it appears that at the moment MassMailer is only able to execute a single SQL Query after email is sent.

    Well add this functionality in a week or so.

Posting Permissions

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