英文:
Select Product_ID And Sum(Qty) From Another Table
问题
这是您提供的查询代码的翻译：
select a.Product_ID , sum(b.Qty)
from recipe_tbl a
left join prepared_tbl b
on b.Product_ID = a.Product_ID
group by a.Product_ID
这个查询试图计算每个产品的已准备数量，但它需要稍作修改以生成您所需的结果。以下是经过修正的查询：
select a.Product_ID, a.Product_Name, a.Req_Qty, a.Units, sum(b.Qty) as Prepared, (a.Req_Qty - sum(b.Qty)) as Remaining
from recipe_tbl a
left join prepared_tbl b
on a.Product_ID = b.Product_ID
group by a.Product_ID, a.Product_Name, a.Req_Qty, a.Units
这个查询将为您生成所需的结果，包括产品ID、产品名称、所需数量、单位、已准备数量和剩余数量。
英文:
I have 2 tables, one is a recipe_tbl, and second is prepared_tbl as follow:
recipe_tbl
| Product_ID | Product_Name | Req_Qty | Units |
|---|---|---|---|
| F-0001 | Flour | 120 | gr |
| S-0001 | Sugar | 100 | gr |
prepared_tbl
| Trans_Code | Trans_Date | Product_ID | Qty | Units |
|---|---|---|---|---|
| t-2302-001 | 2023-02-16 | F-0001 | 10 | gr |
| t-2302-002 | 2023-02-16 | F-0001 | 10 | gr |
| t-2302-003 | 2023-02-16 | S-0001 | 10 | gr |
What I want is to generate sum as follow:
| Product_ID | Product_Name | Req_Qty | Units | Prepared | Remaining |
|---|---|---|---|---|---|
| F-0001 | Flour | 120 | gr | 20 | 100 |
| S-0001 | Sugar | 100 | gr | 10 | 90 |
How to do that in query?
This is my query, but it doesn't work :
select a.Product_ID , sum(b.Qty)
from recipe_tbl a
left join prepared_tbl b
on b.Product_ID = a.Product_ID
group by a.Product_ID
Please help we with the correct query, thank you in advance!
select a.Product_ID , sum(b.Qty)
from recipe_tbl a
left join prepared_tbl b
on b.Product_ID = a.Product_ID
group by a.Product_ID
答案1
得分: 0
你可以使用以下的SQL查询来生成你想要的结果：
SELECT
r.Product_ID,
r.Product_Name,
r.Req_Qty,
r.Units,
SUM(p.Qty) AS 已准备数量,
r.Req_Qty - SUM(p.Qty) AS 剩余数量
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;
为了安全起见，在'prepared_tbl'中没有匹配行时，使用COALESCE返回0。请查看下面带有COALESCE函数的查询：
SELECT
r.Product_ID,
r.Product_Name,
r.Req_Qty,
r.Units,
COALESCE(SUM(p.Qty), 0) AS 已准备数量,
r.Req_Qty - COALESCE(SUM(p.Qty), 0) AS 剩余数量
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;
英文:
You can use the following SQL query to generate the results you're looking for:
SELECT
r.Product_ID,
r.Product_Name,
r.Req_Qty,
r.Units,
SUM(p.Qty) AS Prepared,
r.Req_Qty - SUM(p.Qty) AS Remaining
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;
To be in the safe side, when there are no matching rows in the 'prepared_tbl' use COALESCE to return 0. Check below query with COALESCE function:
SELECT
r.Product_ID,
r.Product_Name,
r.Req_Qty,
r.Units,
COALESCE(SUM(p.Qty), 0) AS Prepared,
r.Req_Qty - COALESCE(SUM(p.Qty), 0) AS Remaining
FROM recipe_tbl r
LEFT JOIN prepared_tbl p ON r.Product_ID = p.Product_ID
GROUP BY r.Product_ID, r.Product_Name, r.Req_Qty, r.Units;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论