Post reply

Calc percent of count(1 value) to total

  • February 10, 2014 at 12:29 pm

    #283538

    Hi I am trying to think of the best way to calculate a count of 'no' values vs the count of total values. Also, if there aren't any 'no' values I need to gererate a 0%

    this is what I have so far:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#temp_percent') IS NOT NULL

    DROP TABLE #temp_percent

    --===== Create the test table with

    CREATE TABLE #temp_percent

    (

    value VARCHAR(50)

    )

    INSERT INTO #temp_percent

    --(STATUSTRACKING)

    SELECT 'no' UNION ALL

    SELECT 'no' UNION ALL

    SELECT 'no' UNION ALL

    SELECT 'no' UNION ALL

    SELECT 'no' UNION ALL

    SELECT 'no' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes' UNION ALL

    SELECT 'yes'

    select

    value, (Count(value)* 100 / (Select Count(*) From #temp_percent)) as Score

    from #temp_percent

    group by value

  • February 10, 2014 at 12:37 pm

    #1687662

    Maybe something like this could help.

    SELECT (COUNT( NULLIF(value, 'yes')) --eliminates yes values

    / (COUNT(*) * 1.0)) * 100 AS no_percentage ,

    (COUNT( NULLIF(value, 'no')) --eliminates no values

    / (COUNT(*) * 1.0)) * 100 AS yes_percentage,

    COUNT(*)

    FROM #temp_percent

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 10, 2014 at 1:24 pm

    #1687675

    Hi Luis, that works, thank you,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply