Post reply

SUM Not working

  • January 26, 2007 at 9:13 am

    #171940

    Can anyone help with this one?

    I am doing a straitforward sum on a table with character, integer and money type columns (see code), but it does not aggregrate (sum) the rows. Any ideas?

    SELECT TOP 100 PERCENT

      CustomerID, SlsRep, Season,

      Division, StyleNum, ColorNum,

      cast(NULL as char(3)) AS F06Flag,

      cast(NULL as char(3)) AS S07Flag,

      cast('F07' as char(3)) AS F07Flag,

      SUM(FcstQty) AS FcstQty, SUM(WholesaleDollars) AS WholesaleDollars,

      @CrntSubFcst AS CrntSubFcst ,

      SUM(SubFcst1Qty)AS SubFcst1Qty, SUM(SubFcst1WholesaleDollars)AS SubFcst1WholesaleDollars,

      SUM(SubFcst2Qty)AS SubFcst2Qty, SUM(SubFcst2WholesaleDollars)AS SubFcst2WholesaleDollars,

      SUM(SubFcst3Qty)AS SubFcst3Qty, SUM(SubFcst3WholesaleDollars)AS SubFcst3WholesaleDollars

    INTO dbo.F07FcstSlsRepDomesticTbl

    FROM dbo.F07FcstSlsRepDomesticTbl_tmp

    GROUP BY

      CustomerID, SlsRep, Season,

      Division, StyleNum, ColorNum, CrntSubFcst,F06Flag,

      S07Flag, F07Flag

    ORDER BY

      CustomerID, SlsRep, Season,

      Division, StyleNum, ColorNum

  • January 26, 2007 at 9:19 am

    #685874

    Can you post the DDL of the table involved and some sample data?


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • January 26, 2007 at 9:26 am

    #685877

    >>but it does not aggregrate (sum) the rows

    Do the columns being SUM()'ed allow NULL ? Summing a NULL value along with other values always returns NULL as the SUM() result.

    Also, what's with the TOP 100 PERCENT and ORDER BY ?

    Is this being used to force an ORDER BY in a view ? If so, be aware that this is a 'hack', and is not supported in SQL2K5 and beyond.

    [Edit] Oops, I see it's a SELECT .. INTO, not a view. What are you hoping to achieve by ordering results INTO a new table ? The ordering does not guarantee anything about the physical row ordering in the destination table.

     

     

  • January 26, 2007 at 11:28 am

    #685920

    "Do the columns being SUM()'ed allow NULL ? Summing a NULL value along with other values always returns NULL as the SUM() result."

    That's not true.  SUM() and most other aggregate functions discard NULLs during the aggregation process.  The exception to this rule is COUNT(*) which counts all rows, whether they are NULL or not.

  • January 26, 2007 at 11:40 am

    #685923

    Doh. You are correct of course.

    Mental note to self: no forum postings before 1st coffee of the day.

     

  • January 26, 2007 at 12:13 pm

    #685934

    Your table F07FcstSlsRepDomesticTbl_tmp may be haing these columns.

    CrntSubFcst,F06Flag, S07Flag, F07Flag

    Avoide these in group by and you should be okay.

    Regards,
    gova

  • January 29, 2007 at 10:17 am

    #686205

    If you try this

    create table t(i int)

    insert into t values(null)

    insert into t values(1)

    select sum(i) from t

    You get 1.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply