SQLize | PHPize | SQLtest

CREATE TABLE table1 ( box_id character varying(50), name character varying(50), another_name character varying(50), product character varying(60), incoming character varying(15), output character varying(15) ); INSERT INTO table1 ( box_id, name, another_name, product, incoming, output ) VALUES ( 1, 'box1', null, 'PRODUCT1', '5', null ), ( 2, 'box2', null, 'PRODUCT2', '6', null ), ( 1, null, 'another_box1', 'PRODUCT1', null, '5' ); select * from table1; select box_id, max(name), max(another_name), "product", sum(coalesce(incoming::numeric, 0)) "incoming", sum(coalesce(output::numeric, 0)) "output", sum(coalesce(incoming::numeric, 0)) - sum(coalesce(output::numeric, 0)) "balance" from table1 group by product, box_id order by box_id;