DB-Fiddle Expected Result: In the result above I want to list all itemID and orderID that have shipped_volume > 0. Therefore, I went with this query: It gives me almost the result I am looking for. The only issue I have is that it puts a 0 for all rows in column sum(order_volume) which is caused by the WHERE condition.

Display value in column although it is excluded by a WHERE condition over another column

CREATE TABLE operations (
    id int auto_increment primary key,
    orderID VARCHAR(255),
    itemID VARCHAR(255),
    event_date DATE,
    order_volume INT,
    shipped_volume INT
);

INSERT INTO operations
(itemID, orderID, event_date, order_volume, shipped_volume
)
VALUES 
("Item_01", "Order_XYZ", "2020-05-01", "600", "0"),
("Item_01", "Order_XYZ", "2020-05-18", "0", "315"),
("Item_01", "Order_XYZ", "2020-05-19", "0", "100"),
("Item_01", "Order_MTE", "2020-08-15", "400", "0"),
("Item_01", "Order_OBD", "2020-08-21", "500", "0"),
("Item_01", "Order_OBD", "2020-11-17", "0", "380"),

("Item_02", "Order_TLP", "2020-02-02", "500", "0"),
("Item_02", "Order_TLP", "2020-02-10", "0", "175"),
("Item_02", "Order_ADF", "2020-03-27", "100", "0"),

("Item_03", "Order_BBI", "2020-03-12", "700", "0"),

("Item_04", "Order_DXR", "2020-12-09", "260", "0"),
("Item_04", "Order_DXR", "2020-12-15", "0", "110"),
("Item_04", "Order_DXR", "2020-12-15", "0", "60"),
("Item_04", "Order_FGU", "2020-12-15", "0", "80");

Expected Result:

    itemID    |    orderID    |      sum(order_volume)   |      sum(shipped_volume)  
--------------|---------------|--------------------------|---------------------------------------
    Item_04   |   Order_DXR   |            260           |             250
    Item_02   |   Order_TLP   |            500           |             175
    Item_01   |   Order_XYZ   |            600           |             415
    Item_01   |   Order_OBD   |            500           |             380

In the result above I want to list all itemID and orderID that have shipped_volume > 0.
Therefore, I went with this query:

SELECT 
itemID,
orderID,
sum(order_volume),
sum(shipped_volume)
FROM operations
WHERE shipped_volume > 0
GROUP BY 1,2;

It gives me almost the result I am looking for.
The only issue I have is that it puts a 0 for all rows in column sum(order_volume) which is caused by the WHERE condition.

What do I need to change so it also displays the sum(order_volume) of all items and orders that have a shipped_volume > 0?

Answer

This may be what you are looking for if i understand correctly :

SELECT 
  itemID,
  orderID,
  sum(order_volume),
  sum(shipped_volume)
FROM operations
GROUP BY 1,2
HAVING sum(shipped_volume) > 0;

FIND A DEMO HERE

Advertisement

Answer