calculate percentange from data of two rows with different select conditions-CodePudding

I have a table that shows the state of tasks of a project, when the task is finished the state = 1 when the task is not finished the state = 0 by default

create tasks(
  id int primary key identitiy(1, 1),
  p_id int references projects(id),
  emp_id int references users(id),
  state int default (0)
)

I'm trying to calculate the percentage of completed tasks by dividing the select count(state) from tasks where p_id = 2 over select count(state) from tasks where p_id = 2 AND state = 0 and multiplying the result by 100 but I can't get the query to work.

I tried a number of solutions I found but I couldn't find a question that had the same conditions on the select statements.

I'm trying to get the result into a java program through JDBC and tried to include allowMultiQueries=true to the connectionURL or use two different query statements and nesting the ResultSets but nothing worked so far

(Edit) this is the method I'm working with in java, when I run it with p_id = 2 it throws the exception The result set is closed

public void projectProgress()throws SQLException, ClassNotFoundException{
    SqlConnection DB = new SqlConnection(); 
    int id = Integer.parseInt(PMprogressFrame.Pnum.getText());
    String sql = "select count(state) as finishedTasks from tasks where P_id = " id " AND state = 1";
    String sql2 = "select count(state) as totalTasks from tasks where p_id = 2";
    
    ResultSet result = DB.st.executeQuery(sql);
    ResultSet result2 = DB.st.executeQuery(sql2);
    while(result.next()){
        while(result2.next()){

        float var1 = result.getFloat("finishedTasks");
        float var2 = result.getFloat("totalTasks");
        float p = (var1/var2)*100;
        PMprogressFrame.progress.setText(p "%");
        }
    }
}

and this is my connection method:

public class SqlConnection {

    Connection conn;
    Statement st;
    ResultSet rs;

    public SqlConnection() throws SQLException, ClassNotFoundException{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String connectionUrl = "jdbc:sqlserver://MEMENTOMORI:1433;databaseName=PMS;user=sa;password=12345;encrypt=false;allowMultiQueries=true";  
         conn = DriverManager.getConnection(connectionUrl);
        st = conn.createStatement();
    
    }

CodePudding user response：

Conditional aggregation would be the simplest method here. You could do this with a SUM divided by a COUNT:

SELECT SUM(CASE state WHEN 0 THEN 1. ELSE 0. END) / NULLIF(COUNT(state),0)
FROM dbo.YourTable
WHERE p_id = 2;

The NULLIF is there to avoid divide by zero errors.

However, AVG might actually be an easier solution:

SELECT AVG(CASE state WHEN 0 THEN 1. ELSE 0. END)
FROM dbo.YourTable
WHERE p_id = 2;

CodePudding user response：

you can try sql

select sum(state)/count(state) 
from tasks where p_id = 2