Databases
StackOverflow2013
Postsdbo
POSQL Server Percentage Calculation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Percentage Calculation
    primarykey
    data
    text
    <p>I am trying to come up with a uniform way to calculate percentage differences between two columns in a single table. Sometimes num1 is zero, in which case the calculated difference should be 100%. Sometimes num2 is zero, in which case the calculated difference should be 100%. Sometimes the differences between num1 and num2 are very large, in which case the calculated difference would be 100%.</p> <p>Here is a sample extract from the table:</p> <pre><code>declare @numtable table ( num1 decimal(10,3) , num2 decimal(10,3) ) insert into @numtable values ( 160 , 161.5 ) insert into @numtable values ( 439 , 377 ) insert into @numtable values ( 100 , 1 ) insert into @numtable values ( 1 , 100 ) insert into @numtable values ( 0 , 20 ) insert into @numtable values ( 20 , 0 ) </code></pre> <p>I made a broken select statement that looks like the following. </p> <pre><code>select num1 , num2 , Abs(100- ( 100 * cast( cast(num1 as decimal(6,3)) / cast(num2 as decimal(10,3) ) as decimal(6,3) ) ) ) as percentdiff from @numtable </code></pre> <p>Which makes this:</p> <pre><code>num1 num2 percentdiff ------------ ------------ ------------- 160.000 161.500 0.900 439.000 377.000 16.400 100.000 1.000 9900.000 1.000 100.000 99.000 0.000 20.000 100.000 </code></pre> <p>The first 3 rows are OK. The fifth row row is OK. The sixth row never shows because it generates a divide by zero error.</p> <p>I want my result set to look like this:</p> <pre><code>num1 num2 percentdiff ------------ ------------ ------------- 160.000 161.500 0.900 439.000 377.000 16.400 100.000 1.000 9900.000 1.000 100.000 9900.000 0.000 20.000 100.000 20.000 20.000 100.000 </code></pre> <p>How should I change my select into something that returns the way I need it?</p> <p>Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USeaswee
    1. USSnowy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Percentage Calculation
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POSQL Server Percentage Calculation
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server Percentage Calculation
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat are you trying to do in the question... Post some sample data and desired output...
      singulars
      1. POSQL Server Percentage Calculation
      1. USTeja
    2. COThe comments next to each insert shows what happens for each row and what I want it to show. Is there a better way to present that?
      singulars
      1. POSQL Server Percentage Calculation
      1. USSnowy
    3. CODid you notice you declared `num2` as an `int` and then tried to insert `161.5`?
      singulars
      1. POSQL Server Percentage Calculation
      1. USMosty Mostacho
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload