1. Registered User
Join Date
May 2013
Posts
35

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. Registered User
Join Date
Jun 2004
Posts
812
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.

3. Registered User
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. Registered User
Join Date
Mar 2007
Posts
627
Originally Posted by satheeshsharma
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?
Originally Posted by satheeshsharma
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).
Originally Posted by satheeshsharma
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. Registered User
Join Date
Jun 2004
Posts
812
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

6. SPAMMER
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
•