Grouping not Grouping

  • Here are the two statements and the relative results.

    select * from details where committed_fund_id = 26515

    SELECT DISTINCT INV_ACCT_ID,LINE_ITEM_NAME,ACTIVITY_ID,COMMITTED_FUND_ID,SUPPLIER,OWNER,INVOICE,SUM(committed_fund) AS COMMITTED_FUND

    FROM details

    where committed_fund_id = 26515

    GROUP BY INV_ACCT_ID,LINE_ITEM_NAME,ACTIVITY_ID,COMMITTED_FUND_ID,SUPPLIER,OWNER,INVOICE

    First result

    inv_acct_idactivity_idcommitted_fund_item_idCOMMITTED_FUND_IDSUPPLIEROWNERcommitted_fundinvoiceline_item_nameline_item_id

    2004351012581526515WINTHROP PRINTING CO INCRenee Conklin17708.52144913.32Merchandising - Brand223

    2004351012684126515WINTHROP PRINTING CO INCRenee Conklin9221.74144913.32Merchandising - Brand223

    2004351012684226515WINTHROP PRINTING CO INCRenee Conklin5648.18144913.32Merchandising - Brand223

    2004351012684326515WINTHROP PRINTING CO INCRenee Conklin13328.83144913.32Merchandising - Brand223

    2004351012684426515WINTHROP PRINTING CO INCRenee Conklin3321.3144913.32Merchandising - Brand223

    2004351012684526515WINTHROP PRINTING CO INCRenee Conklin3725.33144913.32Merchandising - Brand223

    2004351012684626515WINTHROP PRINTING CO INCRenee Conklin3769.83144913.32Merchandising - Brand223

    2004351012684726515WINTHROP PRINTING CO INCRenee Conklin30250.43144913.32Merchandising - Brand223

    2004351012684826515WINTHROP PRINTING CO INCRenee Conklin46788.28144913.32Merchandising - Brand223

    2004351012684926515WINTHROP PRINTING CO INCRenee Conklin11150.88144913.32Merchandising - Brand223

    Second Result:

    INV_ACCT_IDLINE_ITEM_NAMEACTIVITY_IDCOMMITTED_FUND_IDSUPPLIEROWNERINVOICECOMMITTED_FUND

    2004Merchandising - Brand3510126515WINTHROP PRINTING CO INCRenee Conklin144913.3270661.29

    2004Merchandising - Brand3510126515WINTHROP PRINTING CO INCRenee Conklin144913.3274252.03

    I am using SQL 2005 and really stuck. This is only Committed Fund (aka PO) that will not sum and group correctly.

    Thanks, for the help,

    Bill

  • Your getting each individual record because you have activity_id in your select. Remove it and you'll get the totals.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • The activity_id is the same for all detail rows (and besides I need it).

    This grouping work for all other detail records - all but this Committed fund. @!$@%!##

    Thanks for looking at it,

    Bill

  • I think you'll find the problem in one of the text columns... you may have some trailing spaces in some of the descriptions. You can check for those by comparing the DATALENGTH against the LEN of each column... if they are different for any given row, you will have identified the rows and items that are the culprit.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not objecting to Jeff's comment, and I was off on reading the columns so I meant to say the "committed_fund_item_id" column but ...

    Bill Maners (10/19/2007)


    The activity_id is the same for all detail rows (and besides I need it).

    This grouping work for all other detail records - all but this Committed fund. @!$@%!##

    Thanks for looking at it,

    Bill

    and ...

    inv_acct_id activity_id committed_fund_item_id COMMITTED_FUND_ID SUPPLIER OWNER committed_fund invoice line_item_name line_item_id

    2004 35101 25815 26515 WINTHROP PRINTING CO INC Renee Conklin 17708.52 144913.32 Merchandising - Brand 223

    2004 35101 26841 26515 WINTHROP PRINTING CO INC Renee Conklin 9221.74 144913.32 Merchandising - Brand 223

    2004 35101 26842 26515 WINTHROP PRINTING CO INC Renee Conklin 5648.18 144913.32 Merchandising - Brand 223

    Leads me to believe that the "committed_fund_item_id" column is the source of your problem. Honestly, I'm outta here for quite a while so I can't work on a solution for you right now. Hopefully this info helps. Do you really need each "committed_fund_item_id" in the results.

    Maybe Jeff has the time to give the answer. 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Heh... I thought the same thing... but there's two columns that look very much alike...

    Committed_Fund_ID

    Committed_Fund_Item_ID

    I don't see the Committed_Fund_Item_ID being used in the problem GROUP BY query... 'course, the formatting is pretty bad and so are my eyes so I might be mistaken...

    On the lighter side... Jason, I love your avatar... You related to a fellow by the name of "Kilroy" by any chance? 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (10/20/2007)


    Heh... I thought the same thing... but there's two columns that look very much alike...

    Committed_Fund_ID

    Committed_Fund_Item_ID

    I don't see the Committed_Fund_Item_ID being used in the problem GROUP BY query... 'course, the formatting is pretty bad and so are my eyes so I might be mistaken...

    On the lighter side... Jason, I love your avatar... You related to a fellow by the name of "Kilroy" by any chance? 😀

    Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (10/20/2007)


    Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:

    Not in this particular case, as it's not in the SELECT statement. Yep, I had to look at the code several times to be sure as well. I'm a big fan of a line per column in code. 🙂

    What's the datatype of Invoice, by the way? If that's not it, then I'd recommend you simplify the query where it's just a single column with the sum, adding a column at a time until you find the culprit (the first one that returns two rows). At that point, you'll know which column it is, and assuming it's character data, a varbinary will show what the difference is between the two values. Could be something as simple as a leading space, zero instead of letter "O", etc.

  • David McFarland (10/20/2007)


    Jason Selburg (10/20/2007)


    Man, my eyes are going bad too! Now correct me if I'm wrong, but wouldn't the statement fail without having the Committed_Fund_Item_ID in the GROUP BY? :hehe:

    Not in this particular case, as it's not in the SELECT statement. Yep, I had to look at the code several times to be sure as well. I'm a big fan of a line per column in code. 🙂

    ARGH, I'll go and bury my head now... *laughs*

    I just re-read through the original post and it's all to clear now. I'd go with David's approach.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • The invoice column is FLOAT (Not NULL).

    And it is the culprit.

    When I group by INVOICE, I two rows. The following query:

    SELECT DISTINCT

    --INV_ACCT_ID

    --,LINE_ITEM_NAME

    --,ACTIVITY_ID

    --,COMMITTED_FUND_ID

    --,SUPPLIER

    --,OWNER

    --,

    INVOICE

    --,

    --SUM(committed_fund) AS COMMITTED_FUND

    FROM details

    where committed_fund_id = 26515

    GROUP BY

    --INV_ACCT_ID

    --,LINE_ITEM_NAME

    --,ACTIVITY_ID

    --,COMMITTED_FUND_ID

    --,SUPPLIER

    --,OWNER

    --,

    INVOICE

    Returns the following result:

    144913.32

    144913.32

    In addition, I get same result (two rows) when I ask for only the distinct values of INVOICES ???

    SELECT DISTINCT

    INVOICE

    FROM details

    where committed_fund_id = 26515

    Here is the table defintion:

    sp_columns details

    TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPETYPE_NAMEPRECISIONLENGTHSCALERADIXNULLABLEREMARKSCOLUMN_DEFSQL_DATA_TYPESQL_DATETIME_SUBCHAR_OCTET_LENGTHORDINAL_POSITIONIS_NULLABLESS_DATA_TYPE

    CB_Productiondbodetailsinv_acct_id4int1040100NULLNULL4NULLNULL1NO 56

    CB_Productiondbodetailsactivity_id4int1040100NULLNULL4NULLNULL2NO 56

    CB_Productiondbodetailscommitted_fund_item_id4int1040100NULLNULL4NULLNULL3NO 56

    CB_ProductiondbodetailsCOMMITTED_FUND_ID4int1040101NULLNULL4NULLNULL4YES38

    CB_ProductiondbodetailsSUPPLIER-9nvarchar75150NULLNULL1NULLNULL-9NULL1505YES39

    CB_ProductiondbodetailsOWNER-9nvarchar106212NULLNULL1NULLNULL-9NULL2126YES39

    CB_Productiondbodetailscommitted_fund6float158NULL100NULLNULL6NULLNULL7NO 62

    CB_Productiondbodetailsinvoice6float158NULL100NULLNULL6NULLNULL8NO 62

    CB_Productiondbodetailsline_item_name-9nvarchar75150NULLNULL0NULLNULL-9NULL1509NO 39

    CB_Productiondbodetailsline_item_id4int1040100NULLNULL4NULLNULL10NO 56

    Thanks for the help !!

    Bill

  • Floats are not an exact field... Eg, 1.1 cannot be represented in IEEE floating point arithmetic on computers. You should change your invoice field (if it really needs decimal points) to be a DECIMAL/NUMERIC (same thing) field rather than a float field. These fields are exact in their value. Chances are, you're seeing 144913.32 when displaying the value but really one is 144913.3200001 and the other is 144913.32000032 or something. You could convert both to ints but then you're losing the benefit of any index you have in place and are still not really solving the underlying problem.

  • I used the round function on the INVOICE column for the group and all is well.

    Thanks for the support,

    Bill

Viewing 12 posts - 1 through 11 (of 11 total)

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