Percentage Calculation

  • Hello everyone,

    I have a question in regards to how to calculate a percentage of counts between 2 tables in SQL Server.

    Any information would be greatly apprecaited.

    Thank you,

    Peter

    Peter M. Florenzano

    Database Administrator

  • I don't know if this is exactly what you need but here is s guess:

    select cast((select cast(count*) as float) from table1 where YourCondition1) /

    (select cast(count(*) as float) from table2 where YourCondition2) * 100 as numeric(8,4) as Pct

    That one gives a percentage of the number of rows between 2 tables

    I hope this helps

    Bye

    Gabor



    Bye
    Gabor

  • Hi Gabor,

    This worked excellent, thank you. Now I need to truncate the remainder of the numbers to the right of the decimal. Is there a specific function in SQL Server for that?

    Thanks,

    Pete

    Edited by - PFlorenzano on 11/06/2003 10:12:02 AM

    Peter M. Florenzano

    Database Administrator

  • Select Cast (0.01*(Select Count(*) from table1 where YourCondition1)/(Select Count(*) from table2 where YourCondition2) as int )


    * Noel

  • Hello, Peter.

    I had a similar situation just recently.

    What worked for me was to make each count an int or bigint, and set the variable for Percent to be decimal(5,2).

    Table Variables were used so all rows only needed to be queried once and the result set was reused for the counts.

    The function code looks like this (generally):

    GO

    Create Function dbo.udf_PctAppsInBusinessDays

    ( @StartDate DATETIME

    , @EndDate DATETIME

    , @DaysMax INT = 8

    )

    RETURNS

    @retAppCounts TABLE

    ( AppsInBusinessDays int

    , AppsTotal int

    , AppsPercent decimal(5,2)

    )

    AS

    BEGIN

    DECLARE @AppsInBusinessDays int

    DECLARE @AppsTotal int

    DECLARE @AppsPercent decimal(5,2)

    DECLARE @TableVar table

    ( fieldname1 datatype

    , fieldname2 datatype

    , etc....

    )

    INSERT INTO @TableVar

    SELECT columns

    FROM table(s)

    Where condition(s)

    .

    .

    .

    SET @AppsInBusinessDays =

    (Select COUNT(*)

    From @TableVar

    WHERE (BusinessDays <= @DaysMax )

    SET @AppsTotal =

    (Select COUNT(*)

    From @TableVar)

    SET @AppsPercent =

    ((@AppsInBusinessDays*1.0) / @AppsTotal)*100

    INSERT @retAppCounts

    SELECT

    AppsInBusinessDays = NZ(@AppsInBusinessDays,0)

    , AppsTotal = NZ(@AppsTotal,0)

    , AppsPercent = NZ(@AppsPercent,0)

    RETURN

    END

    The returned values are:

    AppsInBusinessDays AppsTotal AppsPercent

    4,520.00 _________ 6,326.00 _ 71.45

    ** NOTE: Watch out for Divide by Zero errors. Right now I can't get to the server drive, or I would have sent the whole code.

    Success to you!

    NJJ

    Norm Johnson

    Norm Johnson

    "Keep smiling ... it gives your face something happy to do
            ... and it makes people wonder what you're up to!"
       

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

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