How to obtain sum of a column from multiple rows with a LEFT JOIN?-CodePudding

I am trying to get the sum of multiple values of a column in a single row with LEFT JOIN.

Here are the tables

CREATE TABLE stocks
(
    id text NOT NULL,
    quantity bigint NOT NULL DEFAULT 1

);

CREATE TABLE transfers
(
    stock_id text NOT NULL,
    quantity bigint NOT NULL DEFAULT 1

);

Here is my query at the moment,

SELECT *
FROM
  (SELECT stocks.id,
          stocks.quantity,
          transfers.quantity AS tquantity,
          stocks.quantity - transfers.quantity AS remaining
   FROM stocks
   LEFT JOIN transfers ON transfers.stock_id = stocks.id) AS stocks
WHERE remaining > 0
  OR remaining IS NULL;

Which returns the following result: result

How can I sum the tquantity of the rows with id1 and subtract that from the stocks.quantity and show them as a single row instead of 2 separate rows?

So instead of this: actual result

I need this: desired result

Here is the dbfiddle, so you can see the data: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b448128cb7e2fc692c023be379d165d9

How can I achieve this desired result optimally?

CodePudding user response：

Try this...

 SELECT stocks.id,
        stocks.quantity,
        sum(transfers.quantity) as tquantity,
        (stocks.quantity - coalesce(sum(transfers.quantity),0)) as remaining 
 FROM stocks
 LEFT JOIN transfers ON transfers.stock_id = stocks.id
 group by stocks.id, stocks.quantity
HAVING (stocks.quantity - coalesce(sum(transfers.quantity),0))  > 0

CodePudding user response：

SELECT id,quantity, sum(tquantity), count(remaining)      FROM (select stocks.id, stocks.quantity, transfers.quantity AS tquantity,
stocks.quantity - transfers.quantity as remaining FROM stocks
LEFT JOIN transfers on transfers.stock_id = stocks.id) AS stocks
WHERE remaining > 0 OR remaining is null
group by id,quantity
order by id asc
 ;

Fiddle here