Grouping not Grouping

Question

Post reply

Grouping not Grouping

Bill Maners

Ten Centuries

Points: 1340
More actions
October 19, 2007 at 3:50 pm

#63106

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

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

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

Bill Maners Ten Centuries Points: 1340 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

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

David McFarland SSChampion Points: 11815 More actions · Answer 7

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.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 8

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

Bill Maners Ten Centuries Points: 1340 More actions · Answer 9

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

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

Ian Yates SSCoach Points: 19738 More actions · Answer 10

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.

Bill Maners Ten Centuries Points: 1340 More actions · Answer 11

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

Thanks for the support,

Bill