MySQL: how to group the sum of two columns that return more than one value?
I have a problem.
I need these two tables to calculate the sum of the column of each table. tables are related to partnumber.
the problem is that each table returns more than one value.
Bill of material AS bom
id finishgood partnumber qty
1 F1920-10 3122E 3
2 F1920-10 AE3030 4
3 F1920-10 3122E 2
4 F1920-10 5538WM 1
5 F1920-10 9803K 2
6 F1920-10 9722F 1
7 F1920-10 9722F 2
8 F1920-10 1001A 1
9 E2020-10 AB123 2
Inventory items AS item
id partnumber LOT onHand
1 3122E M01 105
2 3122E M10 23
3 AE3030 M02 30
4 5538WM M02 15
5 9803K M10 133
6 9722F M15 45
7 9722F M30 55
8 9722F M01 150
9 1001A M10 NULL
This is my request
SELECT bom.finishgood, bom.partnumber, SUM(bom.qty), SUM(item.onHand)
FROM bom
left outer join item ON item.partnumber = bom.partnumber
WHERE bom.partnubmer = 'F1920-10'
GROUP BY bom.partnumber
This is my result
partnumber sum(qty) ERROR sum(onHand)
3122E 10 5*2 128
AE3030 3 ok 30
5538WM 1 ok 15
9803K 2 ok 133
9722F 9 3*3 250
as more values are added, they are added to rows in another table.
This is the perfect result
partnumber sum(bom.qty) sum(items.onHand)
3122E 5 128
AE3030 3 30
5538WM 1 15
9803K 2 133
9722F 3 250
Do they have an idea? I'm disappointed. Thank you for your answer.
source to share
SELECT
DerivedTotalByPart.PartNumber AS [partnumber],
[BOM Total] AS [sum(bom.qty)],
DerivedTotalOnHand.TotalOnHand AS [sum(items.onHand)]
FROM
(
SELECT
bom.partNumber AS [PartNumber],
SUM(bom.qty) AS [BOM Total]
FROM
bom
WHERE
bom.finishgood = 'F1920-10'
GROUP BY
bom.partNumber
) DerivedTotalByPart
LEFT OUTER JOIN
(
SELECT
partnumber,
ISNULL(SUM(onhand), 0) AS [TotalOnHand]
FROM
item
GROUP BY
partnumber
) DerivedTotalOnHand ON DerivedTotalByPart.PartNumber = DerivedTotalOnHand.PartNumber
source to share
Can use UNION
, use sub selects ... Here is one of my favorite MySQL resources: http://www.artfulsoftware.com/infotree/queries.php p>
one parameter ...
SELECT s1 + s2 FROM (
SELECT
(SELECT SUM(item.onHand) FROM item WHERE bom.partnubmer = 'F1920-10') s1,
(SELECT SUM(bom.qty) FROM bom WHERE bom.partnubmer = 'F1920-10') s2
) foo;
source to share
@Christian, Aggregate functions in SQL only return the value of one column and one row. These include SUM (), AVG (), MIN (), MAX (), etc. Therefore, using aggregate functions together with non-aggregate functions before errors. Try to calculate the sum of the items in a separate sql and see the results returned.
Example
SELECT SUM(bom.qty), SUM(item.onHand) FROM bom left outer join item ON item.partnumber = bom.partnumber WHERE bom.partnubmer = 'F1920-10' GROUP BY bom.partnumber
source to share
You can use this query:
SELECT
bom2.partnumber,
bom2.qtySum,
item2.onHandSum
FROM
(
SELECT
partnumber,
sum(qty) qtySum
FROM
bon
GROUP BY
partnumber
) bom2
INNER JOIN (
SELECT
partnumber,
sum(onHand) onHandSum
FROM
item
GROUP BY
partnumber
) item2 ON item2.partnumber = bom2.partnumber
WHERE bom.partnubmer = 'F1920-10'
source to share