PostgreSQL group by with sum

Question

I have this code:

SELECT  m_warehouse_id, qtyonhand 
FROM    adempiere.rv_storage
WHERE   rv_storage.m_product_id=1000412
GROUP BY m_warehouse_id, qtyonhand
ORDER BY m_warehouse_id;

that results in following table:

Now, I want to group by m_warehouse_id so it will return only 2 rows 1000000 and 1000001. qtyonhand will be sumarized and returned as sum. So table should look like:

m_warehouse_id    |    qtyonhand
------------------+---------------
10000000          |            0
10000001          |           10

How to achieve this?

Answer 1

you need to use the aggregate function SUM() and grouped them by m_warehouse_id

SELECT  m_warehouse_id, SUM(qtyonhand) totalQuantity
FROM adempiere.rv_storage
WHERE rv_storage.m_product_id=1000412
GROUP BY m_warehouse_id
ORDER BY m_warehouse_id;