Home > Back-end >  Two Tables/Databases error: Only one expression can be specified in the select list when the subquer
Two Tables/Databases error: Only one expression can be specified in the select list when the subquer

Time:04-20

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

So it displays this: enter image description here

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 SUMs in the SELECT explain that you want those values summed for each group.

  • Related