Testing has proved my amendments to the query are incorrect as the results are not as expected.

I posted a small sample set of data but have removed as I want...

Hi. I resolved the divide by zero by adding:

SELECT ProductId

,COUNT(1) OrderCount

,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/NULLIF(MANCount,0) ELSE AVG(MAN) END MAN

,CASE WHEN COUNT(1) =...

Hi NULL. When I ran against the table I received:

Many thanks for your time and efforts.

Phil.

Hi NULL. So the average cost is calculated by AVG(CASE of ELEMENTS / NumOfOrders ..emmmm.....

Recent testing has proved my original brief was incorrect to return the costs of the last...

Hi. I amended SUM(CASE to AVG(CASE as suggested. Still not quite there. I have attached the output.

The costs against the ProductId I queried all appear as COST1 and are as...

Thanks once again NULL.

Firstly apologies for the error in the attached query. The original query was amended to add an INNER JOIN to table Products, this can be commented out...

Hello again. Testing has thrown up a bit of a problem. Where the OrderId field = ' ' the values are being summed and not averaged?

I have attached more sample...

Many thanks for all your comments. Mr. Celko I am a true SQL novice with many minutes of programming experience 🙂

Do not let the code posted mislead you to the...

Ha! Ha!

Keep up the good work guys. The day I start answering posts is the day I know I have learnt those new tricks.

Regards,

Phil.

Thanks guys and yes I am an old git 🙂

Old Dogs, new tricks....possible?

Thanks,

Phil.

Chris I was just playing with the CTE concept as I am quite new to TSQL.

Moving forward part of the learning process is to know when to employ the correct...

Thanks, not a million miles away from getting one right! 🙂

Phil.

Thanks Chris.

Phil.

Thanks Guys. Null there are 4 product Types.

The table in question is a temporary table created from an excel file (will be dropped at some stage). I need to populate...

Hi. I have joined code provided by Null to an additional table as follows:

FROM Cost

INNER JOIN Products ON dbo.Cost.ProductId = dbo.Products.CrossReference

GROUP BY Cost.ProductId,OrderId) t1) t2

WHERE OrderRank <=5

GROUP BY ProductId

Referring back...