locked
Troubles calculating success rate code 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.

    A developer was kind enough to provide this code for me:

    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 only returns the total no of records in the table (opc) and not the success rate?

    He was very kind to try once more, and came up with this:

    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

     

    This code actually provide the correct numbers, but gives this error:  The Declare SQL construct or statement is not supported.

    I have tried to understand Store procedures as well as data binding, but cannot figure out how to solve this case. I am looking for a SQL query I can use on an aspx page – can anyone out there guide me to where I go wrong on the first code example ?

    Best regards and thanks

    Monday, June 6, 2016 8:34 AM

Answers

  • User77042963 posted
    SELECT CAST( (((SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <4)*1.0/ (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)) *100) AS decimal(10,2)) AS SmallerPercent,
    CAST( (( ( (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)- (SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <4)) *1.0 /  (SELECT count(*) FROM opc WHERE o24 IS NOT NULL) ) *100) AS decimal(10,2)) LargerPercent

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 7, 2016 1:49 PM

All replies

  • User77042963 posted

    Check the spelling of your variable name:

    The @SmallderCount   should be @SmallerCount in your query.

    Monday, June 6, 2016 2:08 PM
  • User1309273214 posted

    Check the spelling of your variable name:

    The @SmallderCount   should be @SmallerCount in your query.

    Right you are, sorry about that - the correct spelled code looks like this:

    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( ((@SmallerCount/ @TotalCount) *100) AS decimal(10,2)) AS SmallerPercent,

    CAST( (( (@TotalCount- @SmallerCount)/ @TotalCount ) *100) AS decimal(10,2)) LargerPercent

    The code works, but I need it to be changed to a code that does not require a stored procedure but just a query I can insert on a aspx page ?

    Thanks

    Tuesday, June 7, 2016 6:48 AM
  • User77042963 posted
    SELECT CAST( (((SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <4)*1.0/ (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)) *100) AS decimal(10,2)) AS SmallerPercent,
    CAST( (( ( (SELECT count(*) FROM opc WHERE o24 IS NOT NULL)- (SELECT count(*) FROM opc WHERE o24 IS NOT NULL AND o24 <4)) *1.0 /  (SELECT count(*) FROM opc WHERE o24 IS NOT NULL) ) *100) AS decimal(10,2)) LargerPercent

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 7, 2016 1:49 PM
  • User1309273214 posted

    Hi limno,

    Thanks, that did the trick - and I even think I understand why Smile

    Best regards

    Friday, June 10, 2016 6:45 AM