Post reply

CAST help please

  • December 12, 2011 at 10:05 pm

    #264732

    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

    #1420554

    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

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 14, 2011 at 3:45 pm

    #1421673

    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

    #1421687

    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