SQL Server Performance Forum – Threads Archive

SUM()

I’d like to return a record set that contains a calculated field in addition to all of the fields in a particular table. This is the script that will return the calculated field: SELECT
[Part].[PartID],
SUM([INVENTORY10].[Quantity])
FROM dbo.[Part], dbo.[Inventory] [INVENTORY10]
WHERE
[INVENTORY10].[PartID] =* [Part].[PartID]
AND [Part].[PartID] = ‘11176’
GROUP BY [Part].[PartID]
I’d like to have all of the fields in the [Part] table returned as well: SELECT * From dbo.[Part] How can I combine these two scripts into one? Thank you!!

Add the additional fields from your Part table to both the SELECT clause and the GROUP BY clause. As long as the fields are char, varchar, nchar, nvarchar, or a numeric type, you should be okay. You could also use:
SELECT [Part].*, (SELECT SUM(dbo.Inventory.[Quantity]) FROM dbo.Inventory WHERE dbo.Inventory.PartID = [Part].[PartID]) AS SumQuantity
FROM dbo.[Part]
WHERE [Part].[PartID] = ‘11176’ or even:
SELECT [Part].*, TMP.SumQuantity
FROM dbo.[Part]
LEFT JOIN (SELECT dbo.Inventory.PartID, SUM(dbo.Inventory.[Quantity]) AS SumQuantity FROM dbo.Inventory GROUP BY dbo.Inventory.PartID) AS TMP ON TMP.PartID = [Part].[PartID]
WHERE [Part].[PartID] = ‘11176’ See what gives you the best response time. Also, check the current syntax for JOINs: the version you’re using is still supported, but obsolete.

Great! Thank you! What about the syntax is obsolete and how has it been replaced? Thanks again!

See where he used LEFT JOIN and you didn’t? Also, don’t use "*", unless you just have some really good reason for using it besides being lazy. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>