December 12, 2011 at 10:05 pm
Can someone please tell me why my decimal has 6 spots and not 4 like I need it to?
USE TSQLFundamentals2008;
SELECT AVG (LineItems)AS AvgLineItems FROM
(SELECT orderid, CAST (COUNT (productid)AS DECIMAL (8,4))
AS LineItems
FROM Sales.OrderDetails
GROUP BY orderid)AS AvgCount
December 13, 2011 at 6:09 am
mrwillcostello (12/12/2011)
Can someone please tell me why my decimal has 6 spots and not 4 like I need it to?USE TSQLFundamentals2008;
SELECT AVG (LineItems)AS AvgLineItems FROM
(SELECT orderid, CAST (COUNT (productid)AS DECIMAL (8,4))
AS LineItems
FROM Sales.OrderDetails
GROUP BY orderid)AS AvgCount
See the below.
BEGIN TRAN
--1,000 Random rows of data
SELECT TOP 1000 IDENTITY(INT,1,1) AS productid,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS orderid
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Your way
SELECT AVG(LineItems) AS AvgLineItems
FROM (SELECT orderid, CAST(COUNT(productid)AS DECIMAL (8,4)) AS LineItems
FROM #testEnvironment
GROUP BY orderid)AS AvgCount
--Correct way to do it
SELECT CAST(AVG(LineItems) AS DECIMAL (8,4)) AS AvgLineItems
FROM (SELECT COUNT(*) AS LineItems,
orderid
FROM #testEnvironment
GROUP BY orderid) avgCount
--Reason your way doesn't work?
SELECT tester, tester+tester AS addition, (tester+tester)/2 AS division,
(tester+tester)*2 AS multiplication
FROM (SELECT 10.0000 AS tester
UNION ALL SELECT 10.0000) a
ROLLBACK
December 14, 2011 at 3:45 pm
The Count(*) will count all rows. Is that the intention? The original uses count(productid) which will skip NULLs.
I think its also sensible to drop temp tables when you're done with them. I tend to use table variables instead because you don't have to worry about clean up.
December 14, 2011 at 4:47 pm
Thank you it has been fixed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply