locked
Calculating succes rate RRS feed

  • Question

  • User1309273214 posted

    Hi all,

    In a table that contains training records, several data fields for each record stores the grading for the specific item as a number (datatype: int, NULL is allowed).

    Grading is from 1 (best) to 5 (failed), and I need to calculate the success rate, i.e. how many percent of the gradings for the specific items is 3 and above and how many percent is 4 and below. It should be based on all the records, so NULL have to be omitted somehow.

    How can I achieve this ?

    Thanks and best regards

    Sunday, March 27, 2016 3:16 PM

Answers

  • User-219423983 posted

    Hi ricas,

    >> but it returns the total no of records in the table (opc)?

    You debug the SQL sting in your VS or SQL Server to check whether each line contains the expected values. If you use the above code in Stored Procedure, you’d better comment out the first line. Or, you could try using below code to achieve your need.

    DECLARE @SmallerCount decimal(10,2) ,@TotalCount decimal(10,2)
    
    SELECT @SmallerCount = count(*) 
    FROM opc 
    WHERE o24 IS NOT NULL AND o24 <4 
    
    SELECT @TotalCount = count(*) 
    FROM opc 
    WHERE o24 IS NOT NULL
    
    
    SELECT CAST( ((@SmallderCount/ @TotalCount) *100) AS decimal(10,2)) AS SmallderPercent, 
    		CAST( (( (@TotalCount- @SmallderCount)/ @TotalCount ) *100) AS decimal(10,2)) LargerPercent
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 8:50 AM

All replies

  • User-219423983 posted

    Hi ricas,

    I have created a demo as below and you could have a look. If they are not what you want, please share your table structure with sample data and the expected result.

    declare @test table (Grading int null)
    insert @test values (1),(2),(null),(3),(4),(null),(5),(1),(2),(3),(4),(null)
    select count(*) from @test t where Grading is not null and Grading <4
    
    declare @SmallderCount decimal(10,2)
    select @SmallderCount = count(*) from @test where Grading is not null and Grading <4
    
    select CAST( @SmallderCount/ COUNT(*) *100 as decimal(10,2)) as SmallderPercent
    from @test where Grading is not null
    select CAST((COUNT(*)- @SmallderCount)/ COUNT(*) *100 as decimal(10,2)) LargerPercent
    from @test where Grading is not null
    

    Best Regards,

    Weibo Zhang

    Monday, March 28, 2016 4:52 AM
  • User1309273214 posted

    Hi Weibo,

    I have amended the query;

    SELECT count(*) FROM opc t WHERE o24 IS NOT NULL AND o24 <4 DECLARE @SmallderCount decimal(10,2) SELECT @SmallderCount = count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <4 SELECT CAST( @SmallderCount/ COUNT(*) *100 AS decimal(10,2)) AS SmallderPercent FROM opc WHERE o24 IS NOT NULL SELECT CAST((COUNT(*)- @SmallderCount)/ COUNT(*) *100 AS decimal(10,2)) LargerPercent FROM opc WHERE o24 IS NOT NULL

    ...but it returns the total no of records in the table (opc)?

    Thanks

    Monday, March 28, 2016 1:47 PM
  • User-219423983 posted

    Hi ricas,

    >> but it returns the total no of records in the table (opc)?

    You debug the SQL sting in your VS or SQL Server to check whether each line contains the expected values. If you use the above code in Stored Procedure, you’d better comment out the first line. Or, you could try using below code to achieve your need.

    DECLARE @SmallerCount decimal(10,2) ,@TotalCount decimal(10,2)
    
    SELECT @SmallerCount = count(*) 
    FROM opc 
    WHERE o24 IS NOT NULL AND o24 <4 
    
    SELECT @TotalCount = count(*) 
    FROM opc 
    WHERE o24 IS NOT NULL
    
    
    SELECT CAST( ((@SmallderCount/ @TotalCount) *100) AS decimal(10,2)) AS SmallderPercent, 
    		CAST( (( (@TotalCount- @SmallderCount)/ @TotalCount ) *100) AS decimal(10,2)) LargerPercent
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 8:50 AM
  • User1309273214 posted

    Hi Weibo, and thanks - I really appreciate this !

    When using the Query builder I get the error message:

    The Declare SQL construct or statement is not supported.

    When I continue and execute the query, I get two numbers that look correct Laughing

    Then I try to Configure the Data Source (in Visual Web Developer), then I need to Define parameters ? Parameter Source and default value, now I am lost ?

    Best regards,

    Tuesday, March 29, 2016 12:22 PM
  • User-219423983 posted

    Hi ricas,

    I’m glad to know that the above code could help you and if it could return the excepted result in your code, you could mark it as answer to close this thread.

    Then I try to Configure the Data Source (in Visual Web Developer), then I need to Define parameters ? Parameter Source and default value, now I am lost ?

    For this issue, it would be a new question in this thread. I suggest you could post it in a new thread for better support and clarify what do you want to bind, the table or the value of “Calculating success rate”?

    If you want bind the value of “Calculating success rate” to data source, you could refer to below links.

    Using graphical front end.

    https://msdn.microsoft.com/en-us/library/k10148y1.aspx?f=255&MSPPError=-2147217396

    Using behind code.

    http://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx

    Best Regards,

    Weibo Zhang

    Tuesday, March 29, 2016 1:01 PM
  • User1309273214 posted

    Hi ricas,

    I’m glad to know that the above code could help you and if it could return the excepted result in your code, you could mark it as answer to close this thread.

    Right you are, Ill try another thread.

    Thanks and best regards

    Wednesday, March 30, 2016 5:39 AM