title: SQL Server How to sum all fields in a row for each row of two queries and then divide them
body: <p>So I have two queries that look like this:</p>
  
  <pre><code>Query1
  
  CPT       Resource    1       2        3       4      5
  2017-06-11  RM1     0.000   28.000  28.000  28.000  28.000
  2017-06-11  RM2     14.000  23.000  28.000  28.000  0.000
  2017-06-11  RM3     0.000   0.000   27.000  27.000  0.000
  2017-06-12  RM1     12.000  34.000  0.000   0.000   28.000
  2017-06-12  RM2     0.000   0.000   0.000   0.000   28.000
  2017-06-12  RM3     17.000  0.000   12.000  0.000   0.000
  
  Query 2
  CPT       Resource    1       2        3       4      5
  2017-06-11  RM1    -23.000  34.000  -22.000 -28.000  7.000
  2017-06-11  RM2    24.000  -15.000   30.000 -18.000 -19.000
  2017-06-11  RM3     0.000   0.000   -27.000  27.000  0.000
  2017-06-12  RM1     12.000  34.000    0.000  13.000  28.000
  2017-06-12  RM2     0.000   0.000   -24.000   0.000  28.000
  2017-06-12  RM3   -27.000   16.000   19.000 -18.000  -21.000
  </code></pre>
  
  <p>How do I code a new select query that will return the date, resource, and the total for each row in the first query. Also, I would like it to return the total for each row in the second query as well but I only want it to add up numbers that are negative and then display them as a positive number (multiply the total by -1 or use abs). After this how would you code so that the total for each dated resource in the first query is divided by the matching total for the same in the second query. For example, dividing the total for RM1 on 2017-06-11 in the first query by the total for the same resource and date in the second query and doing this for every row. And how do I make it so that the new column in the new query displaying the divided totals is called "Daily Cost"</p>
  
  <p>Here is an example of what it would look like if I did it manually for the first two rows:</p>
  
  <pre><code>Query 3
  CPT     Resource        Daily Cost 
  2017-06-11  RM1            1.53
  2017-06-11  RM2            1.78
  </code></pre>
  
  <p>The math in the first row is done by adding 28+28+28+28=112 for the first row in the first query. Then in the second the query the first row is added (-23)+(-22)+(-28)=-73 and then taking the abs value to get 73. Then 112/73 to get 1.53 as the value for the first row in the third query. How do I code this so that sql does it for me for every row?</p>
  
  <p>By the way each table has over 100 dates in it, I just only posted 3 to save time and space. so doing something like this wont work.</p>
  
  <pre><code>    select '2017-06-11', 'RM1', 0.000, 28.000, 28.000, 28.000, 28.000 union all
      select '2017-06-11', 'RM2', 14.000, 23.000, 28.000, 28.000, 0.000 union all
      select '2017-06-11', 'RM3', 0.000, 0.000, 27.000, 27.000, 0.000 union all
  </code></pre>
  
  <p>Im wondering if there is a more efficient way of doing this.</p>