SQL Server How to sum all fields in a row for each row of two queries and then divide them

Question

So I have two queries that look like this:

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

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"

Here is an example of what it would look like if I did it manually for the first two rows:

Query 3
CPT     Resource        Daily Cost 
2017-06-11  RM1            1.53
2017-06-11  RM2            1.78

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?

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.

    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

Im wondering if there is a more efficient way of doing this.

In Query2, look at line 4. If none of the numbers are positive, how do you want to handle the divide by zero problem. — Jason, Jul 12 '17 at 21:11

Jason · Answer 1 · 2017-07-13T15:34:27.750

I got what you are looking for using common table expressions. You should be able to copy the script below and plug in your existing queries. You need to decide what you want to do when none of the values in query 2 are negative, because you can't divide by 0. I am checking for it and if the value is 0, I plug in the total from query 1. This will make the Daily Cost always 1. I'm not sure if that is what you want, but if it isn't; it will need to be modified.

WITH cte1 AS
(
    --Put Query 1 here
),

cte2 AS
(
    --Put Query 2 here
),

--Total up columns 1-5 from Query 1
cte3 AS 
(
SELECT CPT,[Resource],(SUM([1])+SUM([2])+SUM([3])+SUM([4])+SUM([5])) AS 'Total'
FROM cte1
GROUP BY CPT,[Resource]
),

--Total negative numbers from columns 1-5
--and take the absolute value
cte4 AS
(
SELECT CPT,[Resource],(
            ABS(SUM(CASE WHEN [1] < 0 THEN [1] ELSE 0 END)+
                SUM(CASE WHEN [2] < 0 THEN [2] ELSE 0 END)+
                SUM(CASE WHEN [3] < 0 THEN [3] ELSE 0 END)+
                SUM(CASE WHEN [4] < 0 THEN [4] ELSE 0 END)+
                SUM(CASE WHEN [5] < 0 THEN [5] ELSE 0 END))
                ) AS 'Total'
FROM cte2
GROUP BY CPT,[Resource]
)

--Join cte3 and cte4. Then divide the total from
--cte3 by cte4
--IMPORTANT: You must take account for the divide by zero rule
--I am checking to see if cte3 and cte4 is 0 and if it is, I'm
--changing them to 1. It should never reach that point though
--because of the where clause omitting them.
SELECT cte3.CPT,cte3.[Resource],CAST(ROUND(CASE WHEN cte3.Total = 0 
                                THEN 1 ELSE cte3.Total END/
                                CASE WHEN cte4.Total = 0 
                                THEN 1 ELSE cte4.Total END,2) 
                                AS DECIMAL (5,2))  'Daily Cost'
FROM cte3
     JOIN cte4 ON cte3.CPT = cte4.CPT AND
                  cte3.[Resource] = cte4.[Resource]
WHERE cte3.Total <> 0 AND cte4.Total <> 0
ORDER BY CPT,[Resource]

Sample Data Results

+-----------+----------+------------+
|   CPT     | Resource | Daily Cost |
+-----------+----------+------------+
|2017-06-11 |   RM1    |   1.53     |
|2017-06-11 |   RM2    |   1.79     |
|2017-06-11 |   RM3    |   2.00     |
|2017-06-12 |   RM1    |   1.00     | --Row that had no negative numbers in Query 2
|2017-06-12 |   RM2    |   1.17     |
|2017-06-12 |   RM3    |   0.44     |
+-----------+----------+------------+

I attempted the code you wrote with the condition for if cte4 is 0, but I am still getting an error message that states 'Divide by 0 error encountered' — Ray, Jul 13 '17 at 15:01
Then the value from Query 1 must be 0 and its trying to do 0/0. What do you want the result to be in that situation? — Jason, Jul 13 '17 at 15:06
I just updated the code to make the value from query 1 `1` when the value is 0 `AND` the value from query 2 `1` when the value is 0. — Jason, Jul 13 '17 at 15:14
is there any way to make the data read 'NULL' when it is 0/0? — Ray, Jul 13 '17 at 15:17
@Ray you can try replacing `1` with `NULL` in the updated code above. I'm not sure what would happen. Let me know if that works. You could also put a `WHERE` clause above the `ORDER BY`. `WHERE cte3.Total <> 0 AND cte4.Total <> 0` That would omit any rows that has 0 in either of the 2 queries. — Jason, Jul 13 '17 at 15:22
@Ray if you do replace 1 with NULL, do not put parenthesis around the word NULL. — Jason, Jul 13 '17 at 15:23
Cool. I'll update the answer with the where clause. Please accept it as the answer if it was the solution to your problem. — Jason, Jul 13 '17 at 15:32

S3S · Answer 2 · 2017-07-12T21:07:23.770

Well you called it a query, so i assume the two queries are actually result sets. In that case, or the case that they are actual tables which conform to the example, you seemingly only have a single row for each resource per day. In that case, you can do something as such, though I got a different result for your second resource:

declare @query1 table (CPT date, 
                       [Resource] varchar(3), 
                       [1] decimal (5,2), 
                       [2] decimal (5,2), 
                       [3] decimal (5,2), 
                       [4] decimal (5,2), 
                       [5] decimal (5,2))
insert into @query1
values
('20170611','RM1',0.0,28.0,28.0,28.0,28.0),
('20170611','RM2',14.0,23.0,28.0,28.0,0.0)

declare @query2 table (CPT date, 
                       [Resource] varchar(3), 
                       [1] decimal (5,2), 
                       [2] decimal (5,2), 
                       [3] decimal (5,2), 
                       [4] decimal (5,2), 
                       [5] decimal (5,2))
insert into @query2
values
('20170611','RM1',-23.0,34.0,-22.0,-28.0,7.0),
('20170611','RM2',24.0,-15.0,30.0,-18.0,-19.0)

select
    q1.CPT
    ,q1.Resource
    ,(q1.[1] + q1.[2] + q1.[3] + q1.[4] + q1.[4])
     /
     abs((case when q2.[1] < 0 then q2.[1] else 0 end + 
          case when q2.[2] < 0 then q2.[2] else 0 end + 
          case when q2.[3] < 0 then q2.[3]  else 0 end + 
          case when q2.[4] < 0 then q2.[4] else 0 end + 
          case when q2.[5] < 0 then q2.[5] else 0 end))
from 
    @query1 q1
    inner join
        @query2 q2 on
        q1.CPT = q2.CPT 
        and q1.Resource = q2.Resource

If these are actual results from a query, then just place them as a subquery...

select
    q1.CPT
    ,q1.Resource
    ,(q1.[1] + q1.[2] + q1.[3] + q1.[4] + q1.[4])
        /
        abs((case when q2.[1] < 0 then q2.[1] else 0 end + 
            case when q2.[2] < 0 then q2.[2] else 0 end + 
            case when q2.[3] < 0 then q2.[3]  else 0 end + 
            case when q2.[4] < 0 then q2.[4] else 0 end + 
            case when q2.[5] < 0 then q2.[5] else 0 end))
from 
    (select ... from ... where ..) q1
    inner join
        (select ... from ... where ...)  q2 on
        q1.CPT = q2.CPT 
        and q1.Resource = q2.Resource

SQL Server How to sum all fields in a row for each row of two queries and then divide them

2 Answers2