How to Return Separate Sums of Columns if Initial Value in row is 0 in SQL

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:

+-----------------+-------------+---------+
| 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)
    END
FROM Sales_Line
GROUP BY
    Customer_Number,
    Part_Number

But 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?

Answer

Use the function SIGN() in the GROUP BY clause:

SELECT Customer_Number, Part_Number,
       SUM(Shipped) Shipped
FROM Sales_Line
GROUP 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) Shipped
FROM Sales_Line
GROUP BY Customer_Number, Part_Number, 
         CASE WHEN Shipped < 0 THEN -1 ELSE 1 END

See the demo.

Advertisement

Answer