locked
SQL Calculate Percentage RRS feed

  • Question

  • User484515399 posted

    Hello every one, I am trying to get the total percentage of a column

    Regions Workbooks Required Workbooks Sent  Workbooks Returned Workbooks Complete
    A 20 21 20 18
    B 33 33 33 30
    C 19 29 18 16
    D 9 18 8 8
    Totals

    Thanks in advance for any tips/solutions.

    Tuesday, May 6, 2008 3:54 AM

Answers

  • User768703680 posted

    Here is the solution

    You select each of that value in particular region A,B,C,D and store that in some variables using some condition

    Then define another variable for total then you total all these values

    define another variable for percentage and divide the above variable by 5

    Then return the last variable.

    Mark As Answer if this helps you

    Further Queries Recommended

    Happy Coding

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 6, 2008 4:15 AM
  • User42921052 posted

    Queries would look something like this:

    select cast(WorkbooksSent as decimal(11,2))/WorkbooksRequired * 100 as PWorkbooksSent
    From TableName

    select cast(Sum(WorkbooksSent) as decimal(11,2))/Sum(WorkbooksRequired) * 100 as PWorkbooksSentTotal
    From TableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 6, 2008 4:22 PM

All replies

  • User768703680 posted

    Here is the solution

    You select each of that value in particular region A,B,C,D and store that in some variables using some condition

    Then define another variable for total then you total all these values

    define another variable for percentage and divide the above variable by 5

    Then return the last variable.

    Mark As Answer if this helps you

    Further Queries Recommended

    Happy Coding

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 6, 2008 4:15 AM
  • User1421348797 posted

    Please elaborate what you mean by "get the total percentage of a column". If it's hard to put words on it - please post a similar table as the one you posted, but with your desired result, and I or someone else can most probably come up with a SQL query to help you out.

    Tuesday, May 6, 2008 6:51 AM
  • User484515399 posted

    Thank you for your response, I am basically trying to write a stored procedure which calculate the percentage of each column using temp tables to create "Percent Complete" table.

    By the Numbers 
    Regions Workbooks Required Workbooks Sent  Workbooks Returned Workbooks Complete
    A 20 20 20 15
    B 33 33 33 27
    C 20 19 18 17
    D 9 9 8 8
    Totals 82 81 79 67
    Percent Complete
    Regions Workbooks Required Workbooks Sent Workbooks Returned Workbooks Complete
    A 20 100% 100% 75%
    B 33 100% 100% 82%
    C 20 95% 90% 85%
    D 9 100% 89% 89%
    Totals 82 99% 96% 82%

    I appeciate your time and if you could advice how to get the "Workbooks Sent" Percentage for Region A and Total of Region (A,B,C&D), I think I can utilize the same for rest of the "Percentage Complete" Table/Report.

    Once again thank you for your time. 

    Tuesday, May 6, 2008 12:08 PM
  • User42921052 posted

    Queries would look something like this:

    select cast(WorkbooksSent as decimal(11,2))/WorkbooksRequired * 100 as PWorkbooksSent
    From TableName

    select cast(Sum(WorkbooksSent) as decimal(11,2))/Sum(WorkbooksRequired) * 100 as PWorkbooksSentTotal
    From TableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 6, 2008 4:22 PM
  • User-1923849625 posted

    Good afternoon,

    I happened to find your SQL code to resolve the Percentage problem and I have a similar question with a different property. Could you please help me with it?

    Here is my question: I am trying to get the percentage (%) of the total SQFT column for each record and name the added-in calculation column as "Allocation". For example, A is occupied 24%  of the Total SQFT (=round(20/82,2). I need to build up the calculation column "Allocation". Please have a look at the table below.

    Thank you very much for your time and help in advance!

    Regions SQFT Allocation
    A 20 24%
    B 33 40%
    C 20 24%
    D 9 11%
    Totals 82

    100%

    Wednesday, July 10, 2013 2:47 PM