Post reply

Calculate a Percentage

  • July 16, 2009 at 6:22 pm

    #155975

    Hello,

    I'm trying to figure out how to calculate a percentage of hours per company. Any help would be appreciated.

    Thanks!

    Here's some test data.

    IF OBJECT_ID('TempDB..#CompanyHours','U') IS NOT NULL

    DROP TABLE #CompanyHours

    CREATE TABLE [dbo].[#CompanyHours](

    [Company] varchar (100) NOT NULL,

    [Hours] [int] NOT NULL,

    )

    INSERT INTO #CompanyHours (Company, Hours)

    SELECT 'Some Company 1',10056 UNION ALL

    SELECT 'Some Company 2',1354 UNION ALL

    SELECT 'Some Company 2',8979 UNION ALL

    SELECT 'Some Company 3',9826 UNION ALL

    SELECT 'Some Company 5',56465 UNION ALL

    SELECT 'Some Company 6',7893 UNION ALL

    SELECT 'Some Company 4',789

    -- Calculate Total Hours and Percentage

    SELECT Company, SUM(Hours) AS 'TotalHours',

    SUM(Hours) / ((SELECT SUM(Hours) FROM #CompanyHours) * 100) AS 'Percent'

    FROM #CompanyHours

    GROUP BY Company

    ORDER BY SUM(Hours) DESC

    DROP TABLE [dbo].[#CompanyHours]

  • July 16, 2009 at 6:32 pm

    #1025968

    [font="Verdana"]Try something like this:

    with

    TotalHours as (

    select sum([Hours]) as [Hours]

    from #CompanyHours

    ),

    CompanyHours as (

    select Company, sum([Hours]) as [Hours]

    from #CompanyHours

    group by [Company]

    )

    select CompanyHours.Company, CompanyHours.[Hours], cast(CompanyHours.[Hours] * 100.00 / TotalHours.[Hours] as decimal(5, 2)) as [Percent]

    from CompanyHours

    cross join TotalHours

    order by CompanyHours.Hours desc

    [/font]

  • July 17, 2009 at 6:32 pm

    #1026486

    Thanks Bruce! I'll give it a try.

  • July 17, 2009 at 11:14 pm

    #1026505

    dave b (7/17/2009)

    Thanks Bruce! I'll give it a try.

    I'm just making sure... you understand why Bruce's solution will work, correct?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2009 at 11:36 pm

    #1026507

    Yeah...I do understand...kinda. 😉 I like the CTE solution. I was just thinking that there was a simpler way with less code.

  • July 18, 2009 at 2:40 am

    #1026516

    DECLARE@Sample TABLE

    (

    Company VARCHAR (100) NOT NULL,

    [Hours] INT NOT NULL

    )

    INSERT@Sample

    SELECT'Some Company 1', 10056 UNION ALL

    SELECT'Some Company 2', 1354 UNION ALL

    SELECT'Some Company 2', 8979 UNION ALL

    SELECT'Some Company 3', 9826 UNION ALL

    SELECT'Some Company 5', 56465 UNION ALL

    SELECT'Some Company 6', 7893 UNION ALL

    SELECT'Some Company 4', 789

    ;WITH Yak (theGroup, Company, [Hours])

    AS (

    SELECTGROUPING(Company) AS theGroup,

    Company,

    SUM([Hours]) AS [Hours]

    FROM@Sample

    GROUP BYCompany,

    [Hours]

    WITH ROLLUP

    HAVINGGROUPING([Hours]) = 1

    )

    SELECTy.Company,

    y.[Hours],

    100.0E * y.[Hours] / (SELECT w.[Hours] FROM Yak AS w WHERE w.theGroup = 1)

    FROMYak AS y

    WHEREy.theGroup = 0

    ;WITH Yak (Company, [Hours])

    AS (

    SELECTCompany,

    SUM([Hours]) AS [Hours]

    FROM@Sample

    GROUP BYCompany,

    [Hours]

    )

    SELECTy.Company,

    y.[Hours],

    100.0E * y.[Hours] / (SELECT SUM(w.[Hours]) FROM Yak AS w)

    FROMYak AS y

    N 56°04'39.16"
    E 12°55'05.25"

  • July 18, 2009 at 6:05 am

    #1026536

    You can try this as well:

    select t1.company, SUM(t1.hours),cast(sum(t1.hours)*100.00/(select SUM([hours]) from #CompanyHours) as decimal(5,2)) as [percent]

    from #CompanyHours t1

    group by t1.company

    order by SUM(t1.hours) desc

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • July 18, 2009 at 7:05 am

    #1026546

    dave b (7/17/2009)

    Yeah...I do understand...kinda. 😉 I like the CTE solution. I was just thinking that there was a simpler way with less code.

    Heh... nah... I was talking about why /100 doesn't work but /100.0 does. If you divide and integer by an integer, you get an integer for an answer... no decimal places.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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