I am trying to write a SQL query that will return unique rows of sums of shipped part numbers per customer depending on if the value is negative or positive. For example:
If we shipped 20 of part Z to customer A twice, and they returned 6 parts back to us, it would display:
x
+-----------------+-------------+---------+| Customer_Number | Part_Number | Shipped |+-----------------+-------------+---------+| A | Z | 20 || A | Z | 20 || A | Z | -6 |+-----------------+-------------+---------+After running query, expected results:
+-----------------+-------------+---------+| Customer_Number | Part_Number | Shipped |+-----------------+-------------+---------+| A | Z | 40 || A | Z | -6 |+-----------------+-------------+---------+The query I have come up with is:
SELECT Customer_Number, Part_Number, CASE WHEN Ship_Quantity > 0 THEN SUM(Ship_Quantity) WHEN Ship_Quantity < 0 THEN SUM(Ship_Quantity) ENDFROM Sales_LineGROUP BY Customer_Number, Part_NumberBut I get error:
Column invalid. Must be a group by column: Ship_Quantity in SELECT LIST.
When I add the “Ship_Quantity” to my GROUP BY, it does not give accurate results:
Original Input:
+-----------------+---------------+---------+| Customer_Number | Part_Number | Shipped |+-----------------+---------------+---------+| A080 | C76 | -11.0 || A080 | C76 | -1.0 || A080 | C76 | -2.0 || A080 | C76 | -1.0 || A080 | C76 | -1.0 || A080 | C76 | 21.0 || A080 | C76 | 79.0 || A080 | C76 | 1.0 || A080 | C76 | 11.0 || A080 | C76 | 99.0 || A045 | X150 | -6.0 || A045 | X150 | -1.0 || A045 | X150 | -11.0 || A045 | X150 | -2.0 || A045 | X150 | -1.0 || A045 | X150 | -1.0 || A045 | X150 | -1.0 || A045 | X150 | 373.0 || A045 | X150 | 12.0 || A045 | X150 | 1.0 || A045 | X150 | 300.0 || A045 | X150 | 146.0 || A045 | X150 | 150.0 || A045 | X150 | 150.0 || A045 | X150 | 200.0 || A045 | X150 | 150.0 || A045 | X150 | 150.0 |+-----------------+---------------+---------+After Query:
+-----------------+---------------+---------+| Customer_Number | Part_Number | Shipped |+-----------------+---------------+---------+| A045 | X150 | 300.0 || A045 | X150 | 373.0 || A080 | C76 | -11.0 || A080 | C76 | -2.0 || A080 | C76 | -3.0 |+-----------------+---------------+---------+How would I go about doing this?
Advertisement
Answer
Use the function SIGN() in the GROUP BY clause:
SELECT Customer_Number, Part_Number, SUM(Shipped) ShippedFROM Sales_LineGROUP BY Customer_Number, Part_Number, SIGN(Shipped)If your database does not support the function SIGN() use a CASE expression:
SELECT Customer_Number, Part_Number, SUM(Shipped) ShippedFROM Sales_LineGROUP BY Customer_Number, Part_Number, CASE WHEN Shipped < 0 THEN -1 ELSE 1 ENDSee the demo.