Problem converting Access (SUM/GROUP BY) query into TSQL equivalent

[TheQuestioner]

Hi All,

I've recently upsized an Access 2000 database to an MSDE 1 SQL server database. I'm having problems with the following query:-

SELECT     
tblProjectQuotes.IDProject,     
tblProjectQuoteTemplateItems.Name AS QuoteTemplateType,     
tblProjectQuoteItems.Value AS CostPriceTotal,
tblProjectQuoteItems.SellPrice AS SellPriceTotal,     
tblProjectQuoteTemplateItems.IsPrivate

FROM     
tblProjectQuoteTemplateItems INNER JOIN
(tblProjectQuotes INNER JOIN tblProjectQuoteItems
ON tblProjectQuotes.ID = tblProjectQuoteItems.IDProjectQuote) ON     
tblProjectQuoteTemplateItems.ID = tblProjectQuoteItems.IDProjectTemplateItem

WHERE     
tblProjectQuoteTemplateItems.IDTemplateItemType=2

ORDER BY 
tblProjectQuoteTemplateItems.IDTemplateItemType;

Although this query works within SQL Server, what I actually want to do is basically return a list of the sum of the [CostPriceTotal] and [SellPriceTotal] fields, grouping by [IDProject] and [QuoteTemplateType] consecutively. I want to include the following fields: [IDProject], [QuoteTemplateType], [CostPriceTotal], [SellPriceTotal], and [IsPrivate] into the resulting recordset.

[CostPriceTotal] is a nvarchar data type, which can contain text as well as numeric data. But as far as this query is concerned, the data retrieved will either be numeric or null.

[SellPriceTotal] is a money data type, which can be numeric or null.

[IsPrivate] is a bit field.

I understand that you can force convert [CostPriceTotal] and [SellPriceTotal] to avoid nulls by using ISNULL([field],0). How do I go about converting this into an aggregate SUM, GROUP by query?

The original Access query is:-

SELECT     
tblProjectQuotes.IDProject,     
tblProjectQuoteTemplateItems.Name AS QuoteTemplateType,     
Sum(tblProjectQuoteItems.Value) AS CostPriceTotal,
Sum(tblProjectQuoteItems.SellPrice) AS SellPriceTotal,     
tblProjectQuoteTemplateItems.IsPrivate

FROM     
tblProjectQuoteTemplateItems INNER JOIN
(tblProjectQuotes INNER JOIN tblProjectQuoteItems
ON tblProjectQuotes.ID = tblProjectQuoteItems.IDProjectQuote) ON
tblProjectQuoteTemplateItems.ID = tblProjectQuoteItems.IDProjectTemplateItem

GROUP BY     
tblProjectQuotes.IDProject,
tblProjectQuoteTemplateItems.Name,
tblProjectQuoteTemplateItems.IsPrivate,                             
tblProjectQuoteTemplateItems.IDTemplateItemType

HAVING 
(tblProjectQuoteTemplateItems.IDTemplateItemType=2)

ORDER BY 
tblProjectQuoteTemplateItems.IDTemplateItemType;

Please help.