CODEWITHSUNDEEP
sqlt-sql
twils14
twils14

Reputation: 45

Issues with Sums - Not Giving Individual Row Sum, Showing Total Sum On Each Row

I am trying to join two reports to find the total freight, weight and dollar amount based on matched customer number, order number, and product code. I originally was summing the freight for each customer num, order num, prod code, weight and dollar amount combo. But for some reason, it is repeating the sum for a customer num/order num/product code for each weight and dollar amount and I cannot figure out why.

I have tried changing what columns are summed in basically every possible combination, but if I sum weight and dollar, then freight is correct and weight and dollar amounts are wrong. If I sum freight, then freight is wrong but weight and dollar are correct.

    SELECT a.cust, a.order_num, a.prod_code, a.WEIGHT as weight, a.AMOUNT as amount, SUM(b.AMOUNT) as freight_amt
    from dbo.[report1] a
    join dbo.[report2] b on a.cust = b.cust and a.order_num = b.order_num and a.prod_code = b.product and a.loc = b.loc
    where a.order_num = '149254'
    group by a.cust, a.order_num, a.prod_code, a.WEIGHT, a.AMOUNT

What I'm expecting is: Customer A, order number 123, prod code 456 has a weight of 10, amount of 200, freight of 76

Customer A, order number 789, prod code 456 has a weight of 15, amount of 150, freight of 90

What is currently showing with my query the way it is: Customer A, order number 123, prod code 456 has a weight of 10, amount of 200, freight of 166

Customer A, order number 789, prod code 456 has a weight of 15, amount of 150, freight of 166

I apologize if this is confusing. I can't give any actual data I'm using or true column names based on company policy so this is the best I can do for now. Thanks in advance.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I am guessing you want union all and group by:

select cust, order_num, prod_code, loc,
       sum(weight),  -- I'm not sure if this should be sum() or max()
       sum(amount), sum(freight_amt)
from ((select cust, order_num, prod_code, loc,
              weight, amount,
              null as freight_amt
       from dbo.report1
      ) union all
      (select cust, order_num, product, loc,
              null as weight, null as amount,
              null as freight_amt
       from dbo.report1
      )
     ) rr
group by cust, order_num, prod_code, loc

Upvotes: 1

Related Questions