Post reply

Calculate Percentage of a column

  • May 19, 2009 at 3:44 am

    #130398

    I currently have a short script that counts the number of non_blanks for a field called Ethnic_Origin. The script is shown below.

    SELECT --LEFT(Provider_ID,3)AS Provider_ID

    COUNT(CASE WHEN Ethnic_Origin = '' THEN 0 ELSE 1 END) AS Ethnicity_Coded

    --,NHS_Number

    --,Practice_Code

    --,Ethnic_Origin

    --,Specialty_Code

    --,Postcode_of_Usual_Address

    --,HRG_Code

    FROM dbo.Acc_and_Emer_CMDS_Data

    WHERE Purchaser_ID LIKE '5K5%' AND month_of_attendance between '200804' AND '200903'

    How can I amend this script so that I can calculate the number of non_blanks/blanks as a percentage.

  • May 19, 2009 at 3:52 am

    #996807

    (COUNT(CASE WHEN Ethnic_Origin = '' THEN 0 ELSE 1 END)/count(*))*100

  • May 19, 2009 at 4:12 am

    #996813

    I think this code COUNT(CASE WHEN Ethnic_Origin = '' THEN 0 ELSE 1 END) will always return COUNT(*) instead of COUNT(NonBlanks). Change the function COUNT to SUM

    ( SUM( CASE WHEN Ethnic_Origin = '' THEN 0 ELSE 1 END ) * 100.00 ) / COUNT( * )

    -- OR

    ( COUNT( CASE WHEN Ethnic_Origin = '' THEN NULL ELSE 1 END ) * 100.00 ) / COUNT( * )

    -- OR

    ( COUNT( NULLIF( Ethnic_Origin, '' ) ) * 100.00 ) / COUNT( * )

    --Ramesh

  • May 19, 2009 at 4:28 am

    #996818

    Many thanks. It works.

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

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