英文:
How to sum a column when a flag is true for the same products
问题
我有下面的表格，我无法解决这个规则：
SELECT product_id,
flag,
SUM (stock) AS stock
FROM stock_table
WHERE product_id = 120104
GROUP BY product_id,
flag;
如果有多个相同的 PRODUCT_ID 列和不同的 FLAG 列（Y 和 N）：
这两行（示例）将合并为一行，FLAG 将等于 Y，STOCK 列将值相加为 55。
如果对于相同的 PRODUCT_ID 只有一个 FLAG（Y 或 N），只需将 STOCK 列相加。
我尝试过使用子查询来解决，但无法成功。
英文:
I have the table below and I am not able to resolve this rule:
SELECT product_id,
flag,
SUM (stock) AS stock
FROM stock_table
WHERE product_id = 120104
GROUP BY product_id,
flag;
product_id flag stock
120104 N 52
120104 Y 3
If there is more than one identical PRODUCT_ID column and different FLAG column (Y and N):
These two lines (example) will become one, the FLAG will be = Y and the STOCK column will sum the values to 55.
If you only have one FLAG (Y or N) for the same PRODUCT_ID, just sum the STOCK column.
I've tried to solve it with subqueries but I couldn't.
答案1
得分: 1
请尝试以下代码：
SELECT product_id,
MAX (flag) AS flag,
SUM (stock) AS stock
FROM stock_table
WHERE product_id = 120104
GROUP BY product_id;
product_id flag stock
120104 Y 55
每当产品只有一个标志时，它将保持不变，当同时存在N和Y时，它将变为max('N', 'Y') = 'Y'。
英文:
Try this:
SELECT product_id,
MAX (flag) AS flag,
SUM (stock) AS stock
FROM stock_table
WHERE product_id = 120104
GROUP BY product_id;
product_id flag stock
120104 Y 55
Whenever it's just a single flag for the product it will stay as is and when there are both N and Y it will become max('N', 'Y') = 'Y'.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论