postgresql - Postgres 查询 GROUP BY

Question

请帮我解决这个问题

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

我想按 prod.default_code 或 quant.product_id 对结果进行分组>

这是结果示例的屏幕截图。我要数量总和

我该怎么做？

Answer 1

如果您只想要列 default_code, product_id, sum_of_qty 那么您可以这样做:

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

如果您只需要 default_code 或 product_id，只需从 GROUP BY 和 SELECT 中删除不必要的字段>.