Postgres query GROUP BY

Question

please help me with this query

SELECT
   quant.id AS id,
   quant.product_id AS product_id,
   quant.location_id AS location_id,
   quant.qty AS qty,
   quant.lot_id AS lot_id,
   quant.package_id AS package_id,
   quant.in_date AS in_date,
   quant.company_id,
   prod.default_code,
   template.categ_id AS categ_id
FROM stock_quant AS quant
JOIN product_product prod ON prod.id = quant.product_id
JOIN product_template template ON template.id = prod.product_tmpl_id
WHERE quant.company_id = 1
AND location_id = 12

I want to group the results by prod.default_code or quant.product_id

Here is a screenshot a sample of result. I want the sum of quantity

How can I do this?

Answer 1

If you just want the column default_code, product_id, sum_of_qty then you can do this :

SELECT
   prod.default_code,
   quant.product_id AS product_id,
   SUM(quant.qty) AS sum_of_qty
FROM stock_quant AS quant
JOIN product_product prod ON prod.id = quant.product_id
JOIN product_template template ON template.id = prod.product_tmpl_id
WHERE quant.company_id = 1
AND location_id = 12
GROUP BY prod.default_code, product_id

If you need default_code only or product_id only, just remove the unnecessary field from both GROUP BY and SELECT.