To calculate average of double variables in dynamic way

Question

I'm new to java.This question may have answers but I could not find one. I have to create a method in java in which I have to retrieve values from database and have to consider those column values where "OFF" is not displayed in database.Then the average of those values has to be calculated.Right now I'm looking at database and manually considering only those column which has some double values instead of "OFF". But now this has to be done dynamically. I have total 8 columns in which I have to take only 3 columns as in other columns no value is there only "OFF" is being displayed ,whose average I'm able to calculate.The code for it is

 try {
   con = getConnection();


   String sql = "exec vcs_gauge @gauge_name=?,@first_rec_time=?,@last_rec_time=?";


   clstmt = con.prepareCall(sql);
   clstmt.setString(1, "vs3_bag");
   clstmt.setString(2, "2014-09-01 10:00:00");
   clstmt.setString(3, "2014-09-01 11:00:00");
   clstmt.execute();
   rs = clstmt.getResultSet();



   while (rs.next()) {
     a4 = rs.getDouble(7);
     a5 = rs.getDouble(8);
     a6 = rs.getDouble(10);
     averageMap3.put(rs.getString(1), (a4 + a5 + a6) / 3.0);

   }


   //System.out.println("valus is" +averageList);

I want now that instead of specifying the columns which i want to take,a condition is executed which checks if any of the retrieved column has string "OFF" value then it shold not be considered for average calculation. I have to calculate the average in row -wise fashion i.e average of all column of a row will be calculated. Thank in advance

skip rows inside that while with https://docs.oracle.com/javase/tutorial/java/nutsandbolts/if.html ? — zapl, Nov 25 '14 at 11:22
@zapl I can't skip any row,I have to skip columns ,but I'm not getting how to write condition for it. — SRY_JAVA, Nov 25 '14 at 11:24

Joseph118 · Answer 1 · 2014-11-25T13:26:31.017

1

you can either execute an sql query which selects everything accept the ones with the value "OFF" or you can first select the the column which might have the 'OFF' value and check if it has 'OFF' or not... if it doesn't, it proceeds with the calculation.

Edit- example:

while (rs.next()) {
    ResultSetMetaData rsmd = rs.getMetaData();
    boolean x = false;
    for(int i = 0; i < rsmd.getColumnCount(); i++) {
        if(rs.getString(i).equals("OFF")){
            x = true;
        }
    }
    if(x == false){
        // edit to dynamically do an average....
        double sum = 0;
        int count = 0;
        for(int i = 0; i < rsmd.getColumnCount(); i++) {
            int type = rsmd.getColumnType(i);
            if(type == Types.DOUBLE) {
                sum = sum + rs.getDouble(i);
                count ++;
            }
        }
        averageMap3.put(rs.getString(1), (sum/count));
    }
}

edited Nov 25 '14 at 13:26

answered Nov 25 '14 at 11:25

Joseph118

505
6
21

I can't run a sql query as I have to use a predefined Stored Procedure given by third party.I even can't first select the columns as the column values may change dynamically as live values are coming in it.I have to select all the columns and then check for OFF ,if OFF exist then leave that column and consider other columns for average calculation. – SRY_JAVA Nov 25 '14 at 11:31
so you need to check the entire result set first then calculate the average – Joseph118 Nov 25 '14 at 11:48
I can't understand how this second if condition will execute.I understood that I will not consider those columns for which x returns true as they have OFF .But how I will get to know that which column have to be retrieved and put in the double variable in seconf if condition.I'm confused. – SRY_JAVA Nov 25 '14 at 11:56
if "OFF" is never found, the second if will execute because it will always stay false. – Joseph118 Nov 25 '14 at 11:59
,so I have to write all columns in second if condition as you wrote for a4 and one more thing I have to calculate the average in row wise fashion i.e average of all column of a row will be calculated then another row . – SRY_JAVA Nov 25 '14 at 12:14
yes exactly! all the code for average goes into the 2nd if statement. – Joseph118 Nov 25 '14 at 12:17
when I do as you said then following error is shown 'The method getColumnCount() is undefined for the type ResultSet' – SRY_JAVA Nov 25 '14 at 12:27
ow yeah, i forgot the part that you need to use ResultSetMetaData http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html ... answer updated – Joseph118 Nov 25 '14 at 12:28
again I have a problem, if I write all the columns in second if condition i.e-'if(x == false){ a4 = rs.getDouble(7); a5 = rs.getDouble(8); a6 = rs.getDouble(10); a7 = rs.getDouble(9); a8 = rs.getDouble(14); a1 = rs.getDouble(11); a2 = rs.getDouble(12); a3 = rs.getDouble(13);'-.Then all columns will come ,and when I calculate average then what will I put in denominator as I will not be using all columns as some have OFF in them.Can you please write the full code for me. – SRY_JAVA Nov 25 '14 at 12:35
@SRY_JAVA this will simply check if the row has "OFF" value in them, if it does.. it will skip the entire row. Now are you telling me that the row will have "OFF" value and you want to still calculate the average? – Joseph118 Nov 25 '14 at 12:41
I'm saying that there are some columns in row which may OFF and some do not, so I have to calculate the average of those values of columns which do not have OFF in that row . If possible provide me the code.As your second if condition is quite confusing. – SRY_JAVA Nov 25 '14 at 12:47
When I do as you said then output for average is {} i.e blank,nothing is being displayed. – SRY_JAVA Nov 25 '14 at 12:57
@SRY_JAVA you can debug using an IDE to find what is causing you the error and help you understand what it is doing! I cant provide any more help... btw make sure the ResultSetMetaData is inside the while loop. – Joseph118 Nov 25 '14 at 13:05
btw `x` will always be `false` because `== "OFF"` and http://stackoverflow.com/questions/513832/how-do-i-compare-strings-in-java :) – zapl Nov 25 '14 at 13:16
@zapl thanks for pointing that out! thought i changed it before – Joseph118 Nov 25 '14 at 13:21

zapl · Accepted Answer · 2014-11-25T13:25:21.410

You'll have to decompose the step that builds the average into multiple steps. For example like:

while (rs.next()) {
    double sum = 0;
    int count = 0;

    if (!"OFF".equals(rs.getString(7))) {
        sum += rs.getDouble(7);
        count += 1;
    }

    if (!"OFF".equals(rs.getString(8))) {
        sum += rs.getDouble(8);
        count += 1;
    }

    if (!"OFF".equals(rs.getString(10))) {
        sum += rs.getDouble(10);
        count += 1;
    }

    double average;
    if (count > 0)
        average = sum / count;
    else
        average = 0; // or whatever the result of all OFF means

    averageMap3.put(rs.getString(1), average);
}

There should be shorter ways of achieving the same but that's a good start.

e.g. same thing but with a loop and use of ? operator.

while (rs.next()) {
    double sum = 0;
    int count = 0;
    for (int column : new int[] { 7, 8, 10 }) {
        if (!"OFF".equals(rs.getString(column))) {
            sum += rs.getDouble(column);
            count += 1;
        }
    }
    double average = count > 0 ? sum / count : 0;
    averageMap3.put(rs.getString(1), average);
}

I have to calculate the average in row wise fashion i.e average of all column of a row will be calculated then another row ... — SRY_JAVA, Nov 25 '14 at 12:12
when i print average then 0.0 is coming as output,which is impossible as there are three rows which do not have OFF. — SRY_JAVA, Nov 25 '14 at 12:43
@SRY_JAVA Sry, I don't know why. You'll have to use a debugger or print intermediate results to find what's wrong :( Above should calculate the average for each row, only taking those elements into account that are not exactly "OFF". I'd print what `sum` and `count` are after each step, and maybe what `rs.getString(??)` is as well. Maybe it is "0"? — zapl, Nov 25 '14 at 13:23

score 0 · Answer 3 · answered Nov 25 '14 at 11:52

You are reading a lot of data you don't need which is going to make it slow and more complicated. A simpler solution is to have just what you need.

String sql = "SELECT key, (col7 + col8 + col9)/3 FROM my_table" +
             " WHERE  on_off <> 'OFF' AND gauge_name=?" +
             " AND first_rec_time>=? AND last_rec_time<=?";

If you have to use this stored procedure, you can put it into a temporary table and select just the result you need.

This will download just the data you need, the key and the average.

To calculate average of double variables in dynamic way

3 Answers3