Using SQL Server I'm trying to multiply two columns of a distinct part number using 2 tables and 2 databases but it gives this error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
This SQL joins two tables in different databases and show the Final Part Number (FinalPartNo) for a bumper and the ancillary parts that it needs to put it together (bolts, brackets etc.)
Query:
SELECT
tb_1.FinalPartNo,
tb_1.SubPartType,
tb_1.SubPart,
tb_1.FinalItemSubPartQuantity,
tb_2.PurchasedOrMfg,
tb_2.SalesWeek1,
tb_2.SalesWeek2
FROM [009Reports].[dbo].[ANC Parts] tb_1
JOIN [555].[cache].[PurchasingSupplyChainNeeds] tb_2 ON tb_1.FinalPartNo = tb_2.ItemNo
If you look at the table and to siplify things, I highlighted 3 part numbers that all use the same SubPart. Two of them use 4 of the FinalSubPartQuantity and one uses 2 in the "install" step. For SalesWeek1 of the highlighted in the image, they sold two of the FinalPartNo which requires 4 FinalSubPartQuantity and had two sales so that totals 8 needed for that week. I don't need the FinalPartNo but added that to show that it's multiple FinalPartNo with the same subpart.
Trying to figure to sum them up with a totals column for each SubPart for that week (for 52 weeks, just showing 2). In this example, 03CSFY-0500350 for SalesWeek1 could total 150 having it on multiple FinalPartNo and multiple steps (Fabricate, Assembly, Install).
So, I tried a subquery to make the SubPart distinct and multiply the FinalSubPartQuantity x SalesWeek1 for TotalSalesWeek1 but getting error. Trying to figure out syntax.
SELECT
tb_1.SubPart,
tb_1.FinalItemSubPartQuantity,
TotalSalesWeek1 = (SELECT DISTINCT(tb_1.SubPart),
tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek1),
TotalSalesWeek2 = (SELECT DISTINCT(tb_1.SubPart),
tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek2)
FROM [009Reports].[dbo].[ANC Parts] tb_1
JOIN [555].[cache].[PurchasingSupplyChainNeeds] tb_2 ON tb_1.FinalPartNo = tb_2.ItemNo
I'm just trying to display:
SubPart/FinalSubPartQuantity/TotalSalesWk1/TotalSalesWk2/TotalSalesWk3/ to week 52. So it just shows the sub part, sum of all the FinalSubPartQuantity amounts for that part for all the different FinalPartNo's and the total sales FinalItemPartQuantity * SalesWeek1, 2, 3...
summarize: subpart and how many sold of that part that week.
CodePudding user response:
You can't set the TotalSalesWeek1
to two columns (DISTINCT(tb_1.SubPart)
and tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek1
).
I would suggest something like the following
SELECT
tb_1.SubPart,
SUM(tb_1.FinalItemSubPartQuantity) FinalItemSubPartQuantity,
SUM(tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek1) TotalSalesWeek1
SUM(tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek2) TotalSalesWeek2
FROM [009Reports].[dbo].[ANC Parts] tb_1
JOIN [555].[cache].[PurchasingSupplyChainNeeds] tb_2 ON tb_1.FinalPartNo = tb_2.ItemNo
GROUP BY tb_1.SubPart
The GROUP BY tb_1.SubPart
at the end says you want each unique SubPart on a row, the SUM
s in the SELECT
explain that you want those values summed for each group.