SQL Server Performance Forum – Threads Archive
Select records where result of sum = 0
Hello everyone, I’m hoping I can get some help on this issue… I’ve created several views that will be used by our corporate office to pull data on a weekly or monthly basis. However, I’m having trouble giving them the type of data they want. Corporate only wants to see the ‘netted’ results of our order quantities. For example lets say I have these records: order_number shipto_key billto_key line_number product_key order_unit_price cost order_line_item_qty order_line_item_amt————- ———– ———— ———– ————- —————– ——— ——————— ———————
1529 OTST-156 28827 100 MEXI201 205.80 295.98 4 823.20
1529 OTST-156 28827 100 MEXI201 205.80 -295.98 -4 -823.20
1529 OTST-156 28827 100 P1293X1A 310.90 268.62 3 932.70
1529 OTST-156 28827 200 P1293X5-2 707.00 1363.37 4 2828.00 (4 row(s) affected) I need the first and second rows to drop off the result set because the [order quantity] and [net sales] effectively zero out. I know this is likely a simple answer but I’m missing something…
Try this: Select order_number
, shipto_key
, billto_key
, line_number
, product_key
, order_unit_price
, cost
, order_line_item_qty
, order_line_item_amt From <tablename>
INNER JOIN (
Select product_key, sum(cost) as sumOfOrderUnitPrice
From <tablename>
Group By product_key
Having sum(cost) <> 0 ) subTable
ON tablename.product_key = subTable.product_key
Why you want to drop of first row? order_line_item_qty is 4 and order_line_item_amt = 823.20
OR may be I am missing something… If my understanding is correct…the following query should work.. SELECT * FROM TABLENAME WHERE order_line_item_qty >0 AND order_line_item_amt>0
MohammedU.
Moderator
SQL-Server-Performance.com
@keithhowes I think I see where you’re going with the HAVING section of the code, however it still returns all four rows instead of just the rows that do not equal zero? Also, I tried changing the SUMmed column in subTable to match order_line_item_qty. I also tried SUMming cost, order_line_item_qty, and order_line_item_amt, but that returned 100+ rows :0) @MohammedU
Actually, I want to drop off the first two(2) rows because the cost, order_line_item_qty, and order_line_item_amt should equal zero when added together. Select
order_number
, od.shipto_key
, od.billto_key
, od.line_number
, od.product_key
, od.order_unit_price
, od.cost
, od.order_line_item_qty
, od.order_line_item_amt From order_data od
INNER JOIN (
Select product_key, sum(order_line_item_qty) as sumOfOrderUnitPrice
From order_data
Group By product_key
Having sum(order_line_item_qty) <> 0 ) subTable ON od.product_key = subTable.product_key where od.order_number = ‘1529’
Thanks for the ideas and the help on this one!
btw, I’m getting ready to go home for the weekend so if you don’t get any more responses from me today, it’s not because I’m ignoring you!
I apologize I made an error on the first attempt and then went back and edited it… Change the Having sum(order_line_item_qty) <> 0 to Having sum(cost) <> 0
and Select product_key, sum(order_line_item_qty) to Select product_key, sum(cost)
I would just copy what I have now in the first post.
Select order_number
, od.shipto_key
, od.billto_key
, od.line_number
, od.product_key
, od.order_unit_price
, od.cost
, od.order_line_item_qty
, od.order_line_item_amt
From order_data od
INNER JOIN (
Select product_key, sum(cost) as sumOfOrderUnitPrice
From order_data
Group By product_key
Having sum(cost) <> 0 ) subTable
ON od.product_key = subTable.product_keywhere od.order_number = ‘1529’ That should do it.
Yep, tried that – all it did was rearrange the rows… ?
[:I] This really will be my last post till Monday, but I hope to ‘see’ you again then. Steve
Try running the subquery on it’s own and see what the calculated value us coming out to be. At least it would give you an idea of why the sum(cost) <> 0 isn’t restricting the records. The having clause is applied to values after the group by happens so I’m pretty sure that is correct but you might try the where clause in it’s place just to see how it reacts. What is the data type for your cost field? You might have a rounding issue… Select product_key, sum(cost) as sumOfOrderUnitPrice
From order_data
Group By product_key
Having sum(cost) <> 0 mmmmmm….. beer!
For whatever reason, it appears the INNER JOIN was causing a problem after removing it the code works as expected. In the brief example I gave the results are what I was expecting, however, in the real view I am seeing numerous negative numbers as a result of this query. I don’t think is normal but I’m having someone close to the data look into it and see what they think. I may have more questions later – Thanks for all your help! Here’s the current code I’m using:
Select
od.order_number
, od.shipto_key
, od.billto_key
, od.line_number
, od.product_key
, od.order_unit_price
, sum(od.cost) as cost
, sum(od.order_line_item_qty) as ord_line_qty
, sum(od.order_line_item_amt) as ord_line_amt From order_data od
where od.order_number = ‘1529’ Group By
od.order_number
, od.shipto_key
, od.billto_key
, od.line_number
, od.product_key
, od.order_unit_price Having sum(cost) <> 0
And here are the results of the code…
order_number shipto_key billto_key line_number product_key order_unit_price cost order_line_item_qty order_line_item_amt
—————- ————– ————— ————- ————– ——————- ———- ——————— ———————-
1569956 OTST-1569956 28827400 100 PC1293X1A 310.90 268.62 3 932.70
1569956 OTST-1569956 28827400 200 PM1293X5 707.00 1363.37 4 2828.00 (2 row(s) affected)
If your overall cost of a product is negative… I would think you might want some one to audit those orders. Unless of course you really are giving away product and paying people to take it off your hands. You might say Having sum(cost) > 0 but all that does is ignore the negative orders which could cause your report results to be skewed a bit. either way Good luck!
Actually the file being used is a historical order file. Any order entered or changed has a record in this file, so if somebody calls in on Monday and orders 10 items of PM123 a record is inserted with an order_line_item_qty of 10. However, let’s say the same individual calls in on Thursday and says they only need 4 of the PM123 units, at this point an additional record is inserted with an order_line_item_qty of -6. If they call and cancel the order the new record would have an order_line_item_qty of -10. Hope that helps explain what’s going on a little better.
There are actually 23 columns in this view, two of which are dates: Order_Date and Transaction_date. If I remove the two date fields from the query the SUM(order_line_item_qty) works like it’s supposed to and I get 11 rows instead of 13 rows ( a +9 and -9 cancel each other out ). However, with the two date fields in the select list I get all 13 records, meaning the SUM didn’t happen. I believe I understand why this is happening… when using the SUM function MSSQL compares the values of each cell in a row and if they are an exact match with another row the SUM happens, otherwise they are treated as a non-distinct rows and returned as normal. If that is the case, my question is how to I get around this? In a previous example you used an INNER JOIN to link the subquery with the query, and I believe this is done in an attmpt to create a ‘unique’ record so the SUM happens, but for whatever reason it’s not working. Any thoughts as to why the INNER JOIN isn’t working?
]]>