MVSFORUMS.com

schintala · Posted: Thu May 26, 2005 9:11 am Post subject: Query Optimization

Is there any better way of writing the below query to calculate the mean value from a table. It takes lot of time. When we impement it it will have lot of issues with huge volume of data. Any ideas please?

kolusu · Posted: Thu May 26, 2005 9:20 am Post subject:

schintala · Posted: Thu May 26, 2005 9:36 am Post subject:

Mean is different from AVG. For e.g let us say if you have set of two rows below.

Table1
COL1
1
2
3
4
5
6

Mean Here: Average of 3 and 4. Then it is 3.5.

Table2
1
2
3
4
5

Mean Here: Middle number which is 3 here in this case.
In otherwords if you have odd count mean would be middle of the numbers. Where as for even count it would be average of middle two numbers.
Hope you got it what I am looking for.

schintala · Posted: Thu May 26, 2005 9:40 am Post subject:

I am sorry I meant for median not mean. I appologize for the confusion.

kolusu · Posted: Thu May 26, 2005 9:47 am Post subject:

schintala · Posted: Thu May 26, 2005 9:58 am Post subject:

Thank you Kolusu. I have gone thru similar approach but it requires creation of temprary table in our case. We avoided it and came out with this query. But this query is not worth in executing on large volume of data. We are looking options of optimizing it. If won't work out we'll have to consider the temporary table.

kolusu · Posted: Thu May 26, 2005 10:06 am Post subject:

schintala · Posted: Thu May 26, 2005 10:22 am Post subject:

Because we are executing the query in two steps to avoid maximum time limit in obtaing the final result. We store the counts in temporary table and the result of median is calculated from the temp table.