Adding Column Values Together in SQL SELECT Statement

I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below:

SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescriptionAS CPU, dbo.MFG_DATA_Parts_CPU.PartCostAS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescriptionAS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCostAS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescriptionAS RAM, dbo.MFG_DATA_Parts_RAM.PartCostAS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescriptionAS HDD, dbo.MFG_DATA_Parts_HDD.PartCostAS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescriptionAS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCostAS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescriptionAS Video, dbo.MFG_DATA_Parts_Video.PartCostAS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescriptionAS OS, dbo.MFG_DATA_Parts_OS.PartCostAS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescriptionAS Modem, dbo.MFG_DATA_Parts_Modem.PartCostAS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescriptionAS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCostAS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescriptionAS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCostAS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescriptionAS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCostAS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescriptionAS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCostAS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100)AS SalePriceFROM dbo.MFG_DATA_MachinesLEFTOUTER JOIN dbo.MFG_DATA_Parts_CaseTypeON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_MotherboardON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_VideoON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_RAMON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_PowerSupplyON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OSON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OpticalDriveON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_ModemON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_HDDON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_FloppyDriveON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CPUON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CardReaderON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID

First thing I would do is to besure you have a value for all the columns your adding together.

2nd thing would be to use IsNull(dbo.MFG_DATA_Parts_FloppyDrive.PartCost, 0) for each of your columns. What this will do is if there is a NULL value in any of the columns then it will replace it with a 0 thus returning a total and not a null value.

|||

That was exactly it! Thanks for the prompt reply